Copy columns until last row from one sheet and paste to the next empty row of another sheet

Question

I'm using the below VBA code which is copying a range from Sheet1 and paste it in the same sheet. However i need to paste the data in the next available row of sheet2.

Private Sub CommandButton1_Click()

Sheets("Sheet1").Range("A1:A5").Copy

Dim lastrow As Long
lastrow = Range("A65536").End(xlUp).Row

Sheets("Sheet2").Activate
Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

Please help me out..

Answer 1

Your code is good but one line you need to change it, that is place Sheets("Sheet2").Activate line before lastrow = Range("A65536").End(xlUp).Row

 Private Sub CommandButton1_Click()

    Sheets("Sheet1").Range("A1:A5").Copy
    Sheets("Sheet2").Activate
    Dim lastrow As Long
    lastrow = Range("A65536").End(xlUp).Row

    Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    End Sub

Answer 2

Try this:

    Private Sub CommandButton1_Click()

Dim lastrow As Long
Dim rng1 As Range
Dim rng2 As Range

lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Set rng1 = Sheets("Sheet1").Range("A1:A5")
Set rng2 = Sheets("Sheet2").Range("A" & lastrow + 1)

rng1.Copy
rng2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

Answer 3

First activate the sheet2 and then find last row

lastrow = Range("A65536").End(xlUp).Row