Previous Topic Next Topic

Copying rows with data from multiple sheets to one master sheet

Closed
comeecoco - Jul 26, 2017 at 12:24 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - 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!!!
Related:

1 response

Answer 1 / 1
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jul 26, 2017 at 03:15 AM
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.
0