Cfrgtkky

Every month end I have to copy data (say rows 4 to the last row of some worksheets (say worksheets 2 - 7, but in fact there are numerous worksheets and rows) to worksheet 1, start the pasting from row 4 of worksheet 1.

I do try my very best to look out the answer from stack overflow and other website. Given I am a layman of VBA Excel, I can't really customize those code to my case.

I would be very grateful if anyone could give me a hand. Thank you.

Below is a fail example I do by myself

Sub test1()

    Workbooks.Open Filename:="file location"

    Worksheets(2).Activate

    a = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row



    For a1 = 3 To a        

        Worksheets(2).Row(a1).Copy

        Worksheets(1).Activate

        Worksheets(1).Rows(a).PasteSpecial

    Next

End Sub

Thank you alex. i amended the code by reference to yours.
I subsequently found writing my code like below does not really efficient while there a around 100 worksheets to be copied and 7 master lists to be pasted. Below is the revised code, it seems more efficient to be written by "do if" function? Say once the VBA copied worksheets 2-10 rows 4 to worksheet 1, then start copying worksheets 12-21 rows 4 to worksheet 21

Sub test2()



'

Workbooks.Open Filename:="file location"



Worksheets(2).Rows("1:3").Copy Destination:=Worksheets(1).Rows(1)

Worksheets(1).Range("A:AG").ColumnWidth = 20

Worksheets(1).Range("AD:AD").ColumnWidth = 65

'

'

Worksheets(2).Activate

a = Cells(ActiveSheet.Rows.Count, 5).End(xlUp).Row



Set rangeToCopy = Worksheets(2).Range(Cells(4, 1), Cells(a, 33))

rangeToCopy.Copy

Dim lastRow As String

lastRow = Worksheets(1).Cells(Rows.Count, "e").End(xlUp).Row + 1

Worksheets(1).Cells(lastRow, 1).PasteSpecial ' lastRow,1



Application.CutCopyMode = False

Set rangeToCopy = Nothing

'

'

'

Worksheets(3).Activate

b = Cells(ActiveSheet.Rows.Count, 5).End(xlUp).Row



Set rangeToCopy = Worksheets(3).Range(Cells(4, 1), Cells(b, 33))

rangeToCopy.Copy

Dim lastRow2 As String

lastRow2 = Worksheets(1).Cells(Rows.Count, "e").End(xlUp).Row + 1

Worksheets(1).Cells(lastRow2, 1).PasteSpecial ' lastRow2,1



Application.CutCopyMode = False

Set rangeToCopy = Nothing



.....to be continued

End Sub

It's easier to copy-insert Range data without any loops, like this:

Set rangeToCopy= worksheets(1).Range(Cells(1, 1), Cells(a, 100))'insert number of columns instead of 100

rangeToCopy.Copy

worksheets(2).Cells(1, 1).PasteSpecial Paste:=xlPasteValues' 1,1 - row, column where u want to insert your data