Please find below, a macro written by me to consolidate worksheets into a master worksheets all within the same workbook. I look forward to suggestion of making it work as is not at the moment. Many thanks.
Code:
Sub worksheetconsolidation()
'Declare all object variable
Dim wbc As Workbook
Dim wsr As Worksheet
Dim ws(1 To 3) As String
Dim i As Integer
'Define object variable declared above
Set wbc = ActiveWorkbook
Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)
Set ws3 = Worksheets(3)
' Create worksheet Master Report
Set wsr = Worksheets.Add(before:=Worksheets(1))
wsr.Name = "Master Report"
' Set up the headings in the Master Report worksheet
wsr.Cells(1, 1).Value = "Consolidated Report"
' Copy the headings from ws1 to the master report
ws1.Cells(1, 1).Resize(1, 8).Copy Destination:=wsr.Cells(2, 1)
nextrow = 3
' loop through all data worksheets and copy over the records into the master report worksheet
For i = 1 To 3
Worksheets(ws(i)).Select
For Each ws(i) In ActiveWorkbook.Worksheets
' figure the final row in each of the dataworksheet
finalrow = ws(i).Cells(Rows.Count, 1).End(xlUp).Row
startrow = 2
'copy the records in each of the data worksheet into the Master Report
ws(i).Cells(startrow, 1).Resize(finalrow, 8).Copy Destination:=wsr.Cells(nextrow, 1)
nextrow = nextrow + 1
Next ws(i)
Next i
' figure out the final row in wsr
wsrfinalrow = wsr.Cells(Rows.Count, 1).End(xlUp).Row
wsrtotalrow = wsrfinalrow + 1
wsr.Cells(wsrtotalrow, 1).Value = "Total"
'sum the value in column E
wsr.Cells(wsrtotalrow, 5).Formula = WorksheetFunction.Sum(wsrfinalrow & "E")
'Auto fill the sum function in column F,G and H
wsr.Cells(wsrtotalrow, 5).Copy Destination:=wsr.Cells(wsrtotalrow, 5).Resize(1, 4)
End Sub
Last edited by a moderator: