How to paste data from one sheet to another and add a new row if there's already a value in using VBA Excel?

Question

I'm extremely new to VBAs and cannot figure out how to add a value to the next row if there's already data previous row. I'm sure I'm overthinking it, but I cannot seem to figure it out. Any help would be appreciated.

Below is the macro I'm using. Not sure if I need to offset the data or maybe add an if then statement of some sort.

Sub Archive_2()
Range("A2").Select
Selection.Copy
Sheets("Campaign Rate").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

First read this article to avoid using select. [How to avoid select?](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Harun24hr, Oct 29 '20 at 09:51

score 0 · Answer 1 · answered Oct 29 '20 at 09:49

Use below sub-

Sub CopyPaste()
Dim sh As Worksheet
Dim lRng As Range

    Set sh = Worksheets("Campaign Rate")
    Set lRng = sh.Cells(sh.Rows.Count, 1).End(xlUp)
    
    Range("A2").Copy
    lRng.Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Set sh = Nothing
Set lRng = Nothing
End Sub

How to paste data from one sheet to another and add a new row if there's already a value in using VBA Excel?

1 Answers1