CODEWITHSUNDEEP
excelvba
KC Moses Lai
KC Moses Lai

Reputation: 1

How can I copy specific rows from several worksheets to another with Excel VBA?

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

Upvotes: 0

Views: 40

Answers (1)

Alexey C
Alexey C

Reputation: 172

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

Upvotes: 0

Related Questions