VBA - copy and paste onto the next empty cell

[macroos]

Hi All.

I was wondering how to paste some selected data that I copied from Sheet1 onto Sheet2 in the same workbook on the next cell that is blank.

So far, I have the following codes:

Sub Step4()
Sheets("Sheet1").Select
Range("B1:L34").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B" & Rows.Count).End(xlUp).Offset (1) To look for the first empty cell in column B
ActiveSheet.Paste
Range("A1").Select
End Sub

I know something is wrong in the searching for the first empty cell and pasting the copied data, but I'm not sure how to fix it.

Thank you in advance.

 

[alansidman]

try this:

Option Explicit


Sub Step4()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr As Long
    lr = s2.Range("B" & Rows.Count).End(xlUp).Row    'finds last row in column B
    s1.Range("B1:L34").Copy s2.Range("B" & lr + 1)    'assumes you wish to start paste in column B
End Sub

 

[macroos]

Works great! Thank you so much!