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.

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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    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 (Excel)

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

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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
  •