CODEWITHSUNDEEP
excelvba
Thomas Lowe
Thomas Lowe

Reputation: 3

Copy all rows with data from one sheet into another

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

Upvotes: 0

Views: 63

Answers (1)

Tim Williams
Tim Williams

Reputation: 166685

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

Upvotes: 2

Related Questions