Consolidating worksheets into a master worksheet

AFOLABI

New member
Joined
Nov 4, 2011
Messages
3
Reaction score
0
Points
0
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:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Uh.. a little more info would probably be beneficial to us... what isn't working exactly?

Can you post a sample workbook?
 
Top