Hi,
I am a newbie at VBA and need help with consolidating multiple sheets into one master sheet. This looks to be a common problem and I have tried searching the forums before deciding to create a post because I couldn't manage to edit the VBA code to fit.
So here's the gist: My headers span A1:AB4 across all sheets, which are named after months (from Jan to Dec). Obviously, the data in each month will vary so the rows in all sheets vary too, but all cells will be filled between columns A to AB. I would like to consolidate all these sheets into a master sheet "Summary". Below's the code I have so far, referenced from here, which I can't seem to get to work since I don't understand the second If loop (the copy and paste).
Would really appreciate any help, many thanks!
I am a newbie at VBA and need help with consolidating multiple sheets into one master sheet. This looks to be a common problem and I have tried searching the forums before deciding to create a post because I couldn't manage to edit the VBA code to fit.
So here's the gist: My headers span A1:AB4 across all sheets, which are named after months (from Jan to Dec). Obviously, the data in each month will vary so the rows in all sheets vary too, but all cells will be filled between columns A to AB. I would like to consolidate all these sheets into a master sheet "Summary". Below's the code I have so far, referenced from here, which I can't seem to get to work since I don't understand the second If loop (the copy and paste).
VBA Code:
Sub merge2()
Dim sh As Worksheet, r As Long
Sheets.Add Before:=Sheets(1)
ActiveSheet.Name = "Summary"
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Summary" Then
If Application.CountA(Sheets("Summary").Range("A1:AB4").EntireRow) = 0 Then
sh.Range("A1:AB4").Copy Sheets("Summary").Range("A1")
End If
sh.Range("A5", sh.Range("AB5").End(xlDown)).Copy
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(6).PasteSpecial xlPasteValues
End If
Next
End Sub
Would really appreciate any help, many thanks!