by Allen Wyatt
(last updated March 1, 2014)
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.
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!
Need to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a ...Discover More
When you work on older workbooks in Excel, you may notice that the name of the worksheet tab and the workbook itself are ...Discover More
Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information ...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.