Copying rows with data from multiple sheets to one master sheet [Closed]

- - Latest reply: vcoolio
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
- Jul 26, 2017 at 03:15 AM
Hi guys,

I'm trying to figure out how to copy rows from multiple sheets with values in column Q to a master sheet.

I have seen a set of codes as per below which works perfectly to copy rows from one sheet(SHEET1) to the master sheet(MASTER):


Sub Copy_Paste()
Set i = Sheets("SHEET1")
Set e = Sheets("MASTER")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("Q" & j))

If i.Range("Q" & j) <> "0" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1
Loop
End Sub


But I'm really new to this macro thing so I'm not sure what needs to be altered in this code to have rows copied from multiple sheets (e.g. SHEET1, SHEET2 & so on) to the master sheet.

Thank you so much!!!
See more 

1 reply

Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
198
0
Thank you
Hello Comeecoco,

Try the following code placed in a standard module and assigned to a button:-

Sub TransferData()

        Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
      If ws.Name <> "Master" Then

With ws.Range("Q1", ws.Range("Q" & ws.Rows.Count).End(xlUp))
        .AutoFilter 1, "<>0"
        .Offset(1).EntireRow.Copy
        Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        End With
        ws.[Q1].AutoFilter
        End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


I'm assuming that you are using "<>0" (is not equal to zero) as the criteria to filter on. You can change this to suit yourself.

I'm also assuming that your Master sheet has the sheet code of Sheet1 (line 13 in the code above).

The code will filter Column Q of each sheet for the criteria ("<>0") and then transfer the relevant rows of data from each sheet to the Master sheet.

I hope that this helps.

Cheerio,
vcoolio.