Copy all rows with data from one sheet into another

Question

I'm trying to copy all rows with data from one sheet into another.

I get a runtime error at the selection.paste line.

Sub move_rows2()
    
    Dim i As Integer, countSheets As Integer, rowCount As Integer
    
    countSheets = Application.Sheets.Count
    
    For i = 1 To countSheets
        Worksheets(i + 1).Select
        Range("A" & Rows.Count).End(xlUp).Select
        rowCount = ActiveCell.Row
        Rows("1:" & rowCount).Select
        Range("A" & rowCount).Activate
        Selection.Copy
        Worksheets(1).Select
        Range("A" & Rows.Count).End(xlUp).Select
        Selection.Offset(2, 0).Select
        Selection.Paste
    Next i
        
End Sub

`Worksheets(i + 1).Select` is going to fail... `i + 1` will be greater than the number of sheets. — BigBen, Jun 22 '20 at 20:52
https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — BigBen, Jun 22 '20 at 20:53

score 2 · Accepted Answer · answered Jun 22 '20 at 20:59

Without the select/activate, and using an explicit workbook reference.

Dim i As Long, wb As Workbook

Set wb = ActiveWorkbook
For i = 2 To wb.Sheets.Count
    With wb.Sheets(i)
        .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Copy _
             wb.Sheets(1).Cells(.Rows.Count, 1).End(xlUp).Offset(2, 0)
    End With
Next i

Copy all rows with data from one sheet into another

1 Answers1