Consolidate xls worksheets from folder into 1 master sheet

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!
hvanderwAsked:
Who is Participating?
 
aikimarkCommented:
This article might be useful.
http:A_2804.html
0
 
Stacy BrownSenior Applications AdministratorCommented:
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 _
          (ThisWorkbook.Sheets.Count)
        x = x + 1
    Wend


ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Open in new window

0
 
hvanderwAuthor Commented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
hvanderwAuthor Commented:
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?
0
 
aikimarkCommented:
I haven't checked that closely with the code, but you can post a question in the article and ask the author.
0
 
hvanderwAuthor Commented:
This was what I needed. Thanks! Of all the times I looked at it I never scrolled down far enough.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.