Results 1 to 2 of 2

Thread: Consolidating worksheets into a master worksheet

  1. #1

    Consolidating worksheets into a master worksheet

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
            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 Ken Puls; 2011-11-16 at 06:18 AM. Reason: Added code tags

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts