Consolidate xls worksheets from folder into 1 master sheet

Posted on 2012-09-19
Medium Priority
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
  • 3
  • 2
  • 2
LVL 14

Expert Comment

by:Stacy Brown
ID: 38417945
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

ID: 38417968
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 38418431
This article might be useful.

Author Comment

ID: 38418970
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 46

Expert Comment

ID: 38419490
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

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

864 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