Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Merging Many Workbooks.
Written by Allen Wyatt (last updated November 25, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Merging Many Workbooks.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
You may want to add, to your worksheet, the date on which a particular workbook was created. Excel doesn't provide a way ...
Discover MoreWhen you save a workbook, you have the opportunity to save a thumbnail image that can be displayed within Windows. Here's ...
Discover MoreWant a quick way to access the workbooks you use most often? You can "pin" the workbooks so they can be opened with a click.
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 © 2024 Sharon Parq Associates, Inc.
Comments