Consolidate xls worksheets from folder into 1 master sheet

Posted on 2012-09-19
Last Modified: 2012-09-21
Hello, I am looking for a macro to consolidate all xls workbooks in a given folder where all workbooks contain a work sheet named "AP Open Uplaod Form" to a master worksheet where I can then modify all the records at once. The folder may change so prompting of the folder would be helpful.
I have limited VBA knowledge and can make edits to customize it once I have it all, but having difficulty getting all the data consolidated.
Thanks in advance for you help!
Question by:hvanderw
    LVL 13

    Expert Comment

    by:Stacy Brown
    Try this code in a blank workbook that will end up being your master:

    Sub CombineWorkbooks()
        Dim FilesToOpen
        Dim x As Integer
        On Error GoTo ErrHandler
        Application.ScreenUpdating = False
        FilesToOpen = Application.GetOpenFilename _
          (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
          MultiSelect:=True, Title:="Files to Merge")
        If TypeName(FilesToOpen) = "Boolean" Then
            MsgBox "No Files were selected"
            GoTo ExitHandler
        End If
        x = 1
        While x <= UBound(FilesToOpen)
            Workbooks.Open Filename:=FilesToOpen(x)
            Sheets().Move After:=ThisWorkbook.Sheets _
            x = x + 1
        Application.ScreenUpdating = True
        Exit Sub
        MsgBox Err.Description
        Resume ExitHandler
    End Sub

    Open in new window


    Author Comment

    Thats close, but I would like to have only the "AP Open Uplaod Form" worksheet from each xls document to be moved into 1 new worksheet in a new xls document. It would also be every document in a folder as opposed to selecting individual files.
    LVL 13

    Expert Comment

    by:Stacy Brown
    LVL 44

    Accepted Solution

    This article might be useful.

    Author Comment

    Thanks aikimark,  I have tried that one before and it works great, except it takes all the sheets from the workbook and I only need the "AP Open Upload Form" sheet. Do you know how to make it so it only does that sheet?
    LVL 44

    Expert Comment

    I haven't checked that closely with the code, but you can post a question in the article and ask the author.

    Author Closing Comment

    This was what I needed. Thanks! Of all the times I looked at it I never scrolled down far enough.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now