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,268
    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

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