how to select and copy range of data through vba

Question

I am getting stuck on a simple line of code. For this section of my procedure I simply want to merge data from one tab to another. Below is the code i have to copy the information from "Page1-2" and paste in at the next blank row of "Page1-1":

Sheets("Page1-2").Activate
ActiveSheet.Range("A5:AJ66000").Copy
Sheets("Page1-1").Activate
Columns("A").Find("", Cells(Rows.Count, "A")).Select
ActiveCell.PasteSpecial

Any help on this issue would be greatly appreciated.

Thanks!

What is the problem with this code? Do you get an error? Do the individual lines of code work? Did you test them with a MsgBox or the console? — moffeltje, Jul 27 '15 at 18:03

score 0 · Answer 1 · edited May 23 '17 at 11:51

I would create a variable to hold the last row in your Page1-1 sheet.

Sub test()
Dim k   As Integer

Sheets("Page1-2").Range("A5:AJ66000").Copy
With Sheets("Page1-1")
    k = .Cells(1048576, 1).End(xlUp).Row
    If k > 1 Then k = k + 1  ' this is so we don't overwrite the last row on previously copied data
    .Cells(k, 1).PasteSpecial
End With
End Sub
    End Sub

Note: It's a good idea to learn how to avoid using .Select, you can see in my code above how to combine those two lines into one.

how to select and copy range of data through vba

1 Answers1