Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Consolidate all worksheets into master workbook

This is a discussion on Consolidate all worksheets into master workbook within the Excel Questions forums, part of the Question Forums category; Hi All, I think this needs a For Each loop, but I am stuck on how to get it accompished. ...

  1. #1
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,105

    Post Consolidate all worksheets into master workbook

    Hi All,

    I think this needs a For Each loop, but I am stuck on how to get it accompished. In the folder I will have about 26 workbooks with all different titles, but the beggining is always the same for the workbook and the worksheet (Bldg). There will be only one ws per wb and the wb I am using to collect all of the worksheets is Final.xls. The code attached works fine, but the only way right now I know how to capture the next wb/ws is with a call. Can somebody help with how to loop through all wbs and return the ws?

    Code:
    Sub CopyAllWrksht()
        Dim MyPath As String, MyFile As String
        On Error GoTo Error_Handler
            MyPath = ActiveWorkbook.Path & "\"
            MyFile = "Bldg LBV.xls"
        If Dir(MyPath & MyFile) = Empty Then
            MsgBox "The file " & MyFile & " was not found", , "File Doesn't Exist"
        Exit Sub
        End If
            Workbooks.Open Filename:=MyPath & MyFile
            Sheets("Bldg LBV").Copy After:=Workbooks("Final.xls").Sheets(1)
            Workbooks(MyFile).Close
            Exit Sub
    Error_Handler:
    End Sub
    Jeff

  2. #2

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Consolidate all worksheets into master workbook

    Hi,

    Code:
    Sub CopyAllWrksht()
    Dim MyPath As String, MyFile As String
    Dim DestFile As Workbook, sWB As Workbook
    Application.ScreenUpdating = 0
    Set DestFile = Workbooks("Final.xls")
    On Error GoTo Error_Handler
        MyPath = ActiveWorkbook.Path & "\"
        MyFile = Dir(MyPath & "*.xls")
    Do While MyFile <> ""
        Set sWB = Workbooks.Open(Filename:=MyPath & MyFile, UpdateLinks:=0)
        sWB.Sheets("Bldg LBV").Copy After:=DestFile.Sheets(1)
        sWB.Close False
        MyFile = Dir()
    Loop
    Error_Handler:
    Application.ScreenUpdating = 1
    End Sub

  3. #3
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,105

    Post Re: Consolidate all worksheets into master workbook

    Hi Kris,

    Thank you for your time. When I ran this all it did was open one of the two workbooks I would like to consolidate into the master workbook, but it didn't do anything but open. Right now for testing I have two workbooks in my folder (Bldg LBV and Bldg1) and both have just one sheet which is named the same as the workbook name. Again this macro you provided just opened Bldg 1 wb, but that's it. Any other suggestions?
    Jeff

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,006

    Default Re: Consolidate all worksheets into master workbook

    Jeff

    I've not tested Kris's code and I'm just off to watch the second rugby match.

    But I would suggest you remove the On Error... stuff.

    That could just be hiding errors an skipping code.

    Then try stepping through the code with F8 to see what is going on.

    There is definitely code in what's been posted for copying a worksheet and closing workbook.
    If posting code please use code tags.

  5. #5

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Consolidate all worksheets into master workbook

    Hi,

    Open VBE window and run the code manually( hit F8) and see what happens for each line.

    In the meantime replace
    Code:
    sWB.Sheets("Bldg LBV").Copy After:=DestFile.Sheets(1)
    with

    Code:
    sWB.Sheets(1).Copy After:=DestFile.Sheets(1)

  6. #6
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,105

    Post Re: Consolidate all worksheets into master workbook

    That's exactly it Kris. Using F8 the error was on the line you indicated and by changing it to your suggestion it works great. Thanks so much. Thanks to you to Norie. Hope the rugby match is good.
    Jeff

  7. #7

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Consolidate all worksheets into master workbook

    Quote Originally Posted by jrb View Post
    That's exactly it Kris. Using F8 the error was on the line you indicated and by changing it to your suggestion it works great. Thanks so much. Thanks to you to Norie. Hope the rugby match is good.
    You are welcome!

  8. #8
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,105

    Post Re: Consolidate all worksheets into master workbook

    Kris,

    Actually there is one more problem. Since the Final.xls workbook is in the same folder the loop wants to open Final.xls even though it is already open so I get the warning, Final.xls is already open. Reopening will cause any changes...Yes or No. How can this be prevented since Final.xls is already open?
    Jeff

  9. #9
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,105

    Post Re: Consolidate all worksheets into master workbook

    Kris,

    Sometimes I don't use my brain very well. Since all the wbs begin with a B I just changed
    Code:
    MyFile = Dir(MyPath & "*.xls")
    To
    Code:
    MyFile = Dir(MyPath & "B*.xls")
    Jeff

  10. #10
    New Member
    Join Date
    Aug 2009
    Posts
    15

    Default Re: Consolidate all worksheets into master workbook

    Hey!

    I am new to using macros in excel, but I think the issue I am trying to solve is similar to the one posted here.

    Every month I will be receiving a data file from each of the fourteen entities which I am monitoring. In each data file there are a set of worksheets containing different information (e.g. one sheet for the Profit & Loss, one sheet for the Balance Sheet, etc). The reporting format is standardized for all the entities so that each data file and the worksheets contained within are exactly the same. (and have the same file and tab names)

    What I need to do is to compile a consolidated report for the fourteen entities, which will sum up the data in each cell and present this as the total for the whole organization in one file.

    Couple of additional challenges:
    - I only need the data summed up for a sub-set of the sheets in each data file (e.g. out of a total of 10 sheets I will only need to have 5 of the sheets consolidated)
    - From the newly created consolidated data I would also be calculating some ratios etc on new sheets, ideally I could set up the spreadsheet so that these are calculated automatically once the data is consolidated and presented on separate worksheets

    Thanks!!!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com