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.
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: