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

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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!!!
Related:

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
212
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.