Combining Worksheets from Many Workbooks

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:

http://www.rondebruin.nl/win/addins/rdbmerge.htm

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7425) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Averaging the Last Numbers in a Column

Need to calculate a running average for the last twelve values in a constantly changing range of values? The formula ...

Discover More

Odd Arrow Key Behavior

Press the up or down arrow keys, and you expect Excel to change which cell is selected. If this doesn't occur on your ...

Discover More

Playing with a Full Deck

Ever need to populate some cells in your worksheet with a range of data, but in random order? Here's a handy macro to get the ...

Discover More

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!

MORE EXCELTIPS (MENU)

Synchronized Workbook and Worksheet Names

When you work on older workbooks in Excel, you may notice that the name of the worksheet tab and the workbook itself are the ...

Discover More

Unbreakable Formula References to Worksheets

Excel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name of ...

Discover More

Viewing Two Worksheets At Once

If you need to work on two worksheets in the same workbook at the same time, Excel makes this rather easy to do. All you need ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share