by Allen Wyatt
(last updated April 4, 2019)
David has several workbooks that have several worksheets in each of them. He would like to combine a certain worksheet (just one) out of each of these workbooks into a new workbook. He knows how to do this manually using Move or Copy Sheet, but he would like a way to do it more automatically, particularly since there may be many workbooks that he needs to "combine" in this way.
There are a number of different ways you can approach this problem, and all of them involve the use of macros. (This should be no surprise—macros are designed to make quick work of tedious manual tasks.)
The following macro is simple in design; it loops through all the currently open workbooks and for each workbook (except the workbook that contains the macro) copy the sheet named "Sheet1" from that workbook to the workbook containing the code.
Sub CopySheets1() Dim wkb As Workbook Dim sWksName As String sWksName = "Sheet1" For Each wkb In Workbooks If wkb.Name <> ThisWorkbook.Name Then wkb.Worksheets(sWksName).Copy _ Before:=ThisWorkbook.Sheets(1) End If Next Set wkb = Nothing End Sub
If you want the macro to grab a different worksheet than Sheet1, simply change the value of the sWksName variable to reflect the worksheet name desired. If you don't know what the name of the worksheet will be, but you know the worksheet to copy will always be the second worksheet in each workbook, then you can use this variation on the macro:
Sub CopySheets2() Dim wkb As Workbook Dim sWksName As String For Each wkb In Workbooks If wkb.Name <> ThisWorkbook.Name Then wkb.Worksheets(2).Copy _ Before:=ThisWorkbook.Sheets(1) End If Next Set wkb = Nothing End Sub
Perhaps the biggest drawback to the approaches thus far is that all the workbooks need to be open. This might not always be feasible. For instance, you could have a hundred different workbooks in a folder and you need to combine a worksheet out of each of them. Opening a hundred workbooks, while technically possible, probably isn't practical for most people. In that case you need to take a different approach.
The following macro, CombineSheets, is interactive in nature. It asks you for several pieces of information, and then adds worksheets to the workbook based upon your responses. It first asks for a path to the worksheets (don't include the trailing slash) and then for a pattern to use for the workbooks. You can specify a workbook pattern using the regular asterisk (*) and question mark (?) wildcards. For instance, a pattern of * would match all workbooks, while a pattern of Budget20?? would return only workbooks that have "Budget20" at the beginning and any two characters after that.
Sub CombineSheets() Dim sPath As String Dim sFname As String Dim wBk As Workbook Dim wSht As Variant Application.EnableEvents = False Application.ScreenUpdating = False sPath = InputBox("Enter a full path to workbooks") ChDir sPath sFname = InputBox("Enter a filename pattern") sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal) wSht = InputBox("Enter a worksheet name to copy") Do Until sFname = "" Set wBk = Workbooks.Open(sFname) Windows(sFname).Activate Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1) wBk.Close False sFname = Dir() Loop ActiveWorkbook.Save Application.EnableEvents = True Application.ScreenUpdating = True End Sub
When you run the macro you are also asked for the name of a worksheet to copy from each matching workbook. Provide a name, and if such a worksheet exists in the workbook it is copied to the beginning of the current workbook.
If you prefer not to create your own macro for combining worksheets, you might consider the RDBMerge add-in created by Excel MVP Ron de Bruin. You can find it for free, here:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7425) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Got a workbook with a lot of worksheets in it? Here's some handy ways to jump to the worksheet you want, alphabetically.Discover More
If you need to save your Excel data at different benchmarks, you might want to use some sort of "versioning" system. Such ...Discover More
You can protect various parts of your worksheets by using the tools built into Excel. One thing you can protect is the ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.