Joy ran into a problem merging quite a few workbooks together. The majority of the workbooks—about 200 of them, all in a single folder—each contain a single worksheet, but some contain more. The worksheets forming each of these workbooks needs to be added to a single workbook.
The easiest way to do merges of this magnitude—particularly if you have to do it often—is with a macro. The following macro displays a dialog box asking you to select the files to merge. (You can select multiple workbooks by holding down the Ctrl key as you click each one.) It loops thru the list you select, opening each one and moving all its worksheets to the end of the workbook with the code.
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
In the process of adding the worksheets to the end of the workbook, Excel will automatically append a (2), (3), etc. when duplicates worksheet names are detected. Any formulas in the book referring to other sheets will also be updated to reflect the new names.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12652) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Merging Many Workbooks.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the ...
Discover MoreWorkbooks can get rather large rather quickly. If you think your workbook has gotten too big too fast, here are some ...
Discover MoreYou can spend a lot of time getting your workbook to look "just right." Wouldn't it be great if Excel was able to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-10-16 08:37:13
Debbie Sala
I was tasked with creating a workbook to track supervisor and temp supervisor qualifications for each of our 4 maintenance shops. That was an easy process, however, the hard part is trying to figure out how to combine the 4 workbooks with multiple worksheets into one master workbook. I can use the macro to merge the worksheets but then I have one workbook with the same sheets 4 different times and no break in them to show the separate shops. Is there a way to actually create a master workbook that contains for workbooks containing multiple worksheets or do I just need to insert a new "sheet" as a shop name tab?
2018-07-08 10:42:13
Chalina Anderson
Such a time-saver ... thank you for posting this, as my task for the weekend was to consolidate and format 14 files into one ... perfect timing! Because my files were in multiple folders, I copied and pasted them into a single folder, then selected all by highlighting the first file and then hovering over the last in the list and pressing SHIFT while left-clicking the mouse. I then had to answer questions about updating links, which is normal. Great tool! Thanks again!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments