Copy/paste range from one worksheet to next available column in another worksheet

Question

I am trying to copy a range of data from one worksheet into another worksheet in the same workbook.

So copy range("A2:B10") in Sheet1 then paste in ("A2") in Sheet2.

Each time the macro is run, I would like the values in ("Sheet 1").Range("A2:B10") to be pasted in consecutive columns, so in the second run, in "B2" "C2" in Sheet 2.

I have the first part, but struggling with the 2nd part:

Sub sbCopyRangeToAnotherSheet()
'Set range
Sheets("Sheet1").Range("A2:B10").Copy  Destination:=Sheets("Sheet2").Range("A2")
'Copy the data
Sheets("Sheet1").Range("A2:B10").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("A2").Select
'Paste in the target destination
ActiveSheet.Paste

Application.CutCopyMode = False
End Sub

Answer 1

This is something that will do the job without using Active and Select:

Option Explicit

Sub TestMe()

    Dim ws1         As Worksheet
    Dim ws2         As Worksheet        
    Dim source      As Range
    Dim target      As Range        
    Dim lastColumn  As Long

    Set ws1 = Worksheets(1)
    Set ws2 = Worksheets(2)

    With ws2
        lastColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column
        If WorksheetFunction.CountA(.Columns(1)) > 0 Then
            lastColumn = lastColumn + 1
        End If
    End With

    Set source = ws1.Range("A2:A10")
    Set target = ws2.Cells(2, lastColumn)

    source.Copy destination:=target
    Application.CutCopyMode = False

End Sub

The trickiest part in the code is to start writing on the correct column on the target worksheet. If the worksheet is empty, then the correct column is the first one. In every other case, the correct column is the last column + 1.

The standard way of finding last used column - Range.End(xlToLeft).Column would always return the first column as a last used column, w/o paying attention whether it is used or not. Thus you need some kind of a check, to know whether it is empty or not: WorksheetFunction.CountA(.Columns(1)) > 0

Answer 2

Your code is odd

This line Sheets("Sheet1").Range("A2:B10").Copy Destination:=Sheets("Sheet2").Range("A2") is copying and pasting all in one line. So your subsequent lines where you .copy .activate .select and .paste are redundant. Your first line already did all of this.

You need to identify the last used column in Row "A" of Sheet2. To do this you can use the .End() method of a range to find the first occupied cell in the row. Then use that cell as your copy Destination:

 Sub sbCopyRangeToAnotherSheet()

     'Declare a variable to hold the cell to which we will paste the data
     Dim pasteCell as Range

     'Set the variable to the first empty cell in Row 2
     Set pasteCell = Sheets("Sheet2").Range("IV2").end(xlToLeft).Offset(,1)

     'Copy and paste
     Sheets("Sheet1").Range("A2:B10").Copy  Destination:=pasteCell
End Sub