Excel.Tips.Net ExcelTips (Menu Interface)

Combining Worksheets from Many Workbooks

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 _
        End If
    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 _
        End If
    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)
        Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
        wBk.Close False
        sFname = Dir()
    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.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Andrew    05 Oct 2016, 05:50
Hi. Could you help me with the code for taking two data ranges / more than one specific fixed cell from multiple workbooks and pasting into a single worksheet
Diana    13 Sep 2016, 20:28
can u look for my code.. bcoz i need to select 2 @ 3 sheet from each of 3 different workbook using wildcard. but the code jump directly to end the loop.

Sub getsheets()
Path = "C:\New folder"
Filename = Dir(Path & "*.xls")
''filename2 = Dir(Path & "LENGTH_CABLE_NY32 FBOM_V1B_ENGLISH.xls")
''filename3 = Dir(Path & "LENGTH_CABLE_NY32W FBOM_V2_ENGLISH.xls")
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
            For Each Sheet In ActiveWorkbook.Sheets
                If Sheet.Name Like "*CABLE*" Then
                Sheet.Copy after:=ThisWorkbook.Sheets("Sheet1")
                End If
                If Sheet.Name Like "Extension_*" Then
                Sheet.Copy after:=ThisWorkbook.Sheets("Sheet2")
                End If
            Next Sheet
                Filename = Dir()
End Sub
Z1234    24 May 2016, 05:58
Hi there, the workbooks that i'm copying all only have one sheet, each named as a number 1-54, they represent 54 weeks of data. I'd like the new workbook to contain all 54 sheets named correctly so that I can keep track of the order. Please can you help me achieve this? Thank you for your code by the way, much appreciated.
Andrew Dykers    06 Apr 2016, 13:12
If your instructions do not apply to all versions of Excel, please tell us before we have waded through them, and not after.
Abi    03 Feb 2016, 07:50
hi...,I need to copy Columns A,D,G, F from multiple workbooks and save it as different sheets of a single workbook, however it should be done using macros in Excel 2007
Ramesh    21 Sep 2015, 00:50
Hi, i need to copy data (not to move) from many workbooks to a master workbook having sheets named Project 01 to 30..
any help
Dinesh Rajah    10 Jul 2015, 05:07
Hi Allen,

is there a way to use wildcard on the worksheets?

for example Sheet??? pulls through Sheet1 & Sheet2

Thanks Dinesh
Vijay Gumbar    10 Jun 2015, 06:11
Hi Allen, Thanks for sharing the add-in. It works just fine.
Sharon    04 Jun 2015, 11:20
Was Guilherme's question ever answered? I'm having the same issue. Thanks.
nick    24 Mar 2015, 14:32
baskar    17 Mar 2015, 16:53
Hi Allen,
I want copy specific workoutsheets from various workbook but the file path will get change every week how can set path and copy the worksheets for example: for first we want get work sheet from folder1 for week 2 we want get sheet from folder like folder will get change how to set source path by form setup
Murthy    11 Mar 2015, 02:05
Hi Allen,

I am looking at combining data from selected cells from Many Workbooks.

Example: Copy data from A2 C5 and D10 and paste the same on A2,A3,A4 of the consolidated workbook.

Can you please help me with this.

Thanks You,
Larry Wilchek    06 Mar 2015, 14:42
How do I consolidate/merge/combine several worksheets that have exactly the same structure/format?
Each worksheet will have text type changes, i.e. <email address>, <item description>, etc. I cannot use any of the standard Data/Consolidate type of functions. I don't want multiple copies onto one new sheet, I want to incorporate the changes from the other sheets into a master sheet, i.e. new email address, etc.
Can anyone help me with this?
Valendar Turner    21 Jan 2015, 06:54
This script works almost as I need for an important project.

I have 200 csv files which currently I open manually in EXCEL 2010. Each has one worksheet. The problem is that 200 is about to become 2000.

I wish to open all in EXCEL, copy the sole worksheet of each into one workbook consisting of 200 worksheets.

Each of the 200 workbooks' worksheets has a different name and none is called Sheet1. (In fact the same as the filename).

Is there a modification of the vba that will allow me to do this?

Thank you

Sam    08 Dec 2014, 15:14
Hi Allen, my query is similar to Nida's, I would like to list the information from one specific sheet in multiple workbooks onto one "master" sheet in a "master" workbook.

Sorry to put the comment on again but I didn't check the Notify me about new comments box 1st time round.
Sam    08 Dec 2014, 15:11
Hi Allen, my query is the same is Nida's, I would like to list the information from one specific sheet in multiple workbooks onto one "master" sheet in a "master" workbook.
Nida    01 Dec 2014, 05:49
What if I want to combine the data of different sheets from different workbooks into 1 new sheet of a new workbook
awyatt    20 Nov 2014, 17:31
Guilherme: It won't work because you are trying to open a *single* file that uses a wildcard (the asterisk) in the filename. You have to have a real, unambiguous filename.

See the tip at http://excel.tips.net/T005598 for one example of a way to step through all the files in a folder and work with them.

Guilherme    19 Nov 2014, 13:44
Hi Allen,

I am trying to use this code but I get the following error:

"Run-time error 424
Object required"

By doing F8 in the code, it looks like the error is in the following line:

Set wBk = Workbooks.Open(sFname)

I got rid of the msgbox to do so, and my code at the moment goes like this:

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 = "V:\umlondon\RESEARCH\Mediabrands Analytics\Clients\ITV\Modelling\z MODEL OUTPUTS"
    ChDir sPath
    sFname = "*"
    sFname = Dir(sPath & "\" & sFname & ".xlsx", vbNormal)
    wSht = "Individual contributions"
    Do Until sFname = ""
        Set wBk = Workbook.Open(sFname)
        Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
        wBk.Close False
        sFname = Dir()
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Am I doing anything wrong?
Do you know what might be missing?

Thank you,

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.