Combining Worksheets from Many Workbooks

by Allen Wyatt
(last updated March 1, 2014)

27

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

Designing Standard Tables

If you have a common table layout that you want to use again and again, you'd benefit by having an easy way to save that ...

Discover More

Setting a Default for the Object Browser

Does it bother you that when you press Ctrl+Page Up or Ctrl+Page Down you aren't always taken to the top of the previous or ...

Discover More

Adjusting Bottoms of Pages

When you allow Word to naturally flow your text through a document, you may find that the text on each page ends at a ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Creating a Copy without Formulas

Excel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from the ...

Discover More

Detecting Types of Sheets in VBA

When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It ...

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

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 5 + 0?

2017-07-25 11:52:10

Arnel

Nothing happened


2017-07-04 03:46:08

Alan Elston

Hi Sniper
Yours is a very typical requirement. There are many ways to do it. Exactly how will depend on your specific data.
Based on your brief description we can only give a general direction:
You could use, for example, the Application.InputBox with option Type:=8, which will ask you to select a range. That could be the cell with the current information of workbook or worksheet name.
There is much information on how to consolidate worksheet data ranges into a single sheet. Try a Google search with, for example, VBA Merge worksheet ranges
If you need specific help, it would probably be best to give specific reduced size sample data showing what you have and what you then want the code to give you from that sample data.
It might be more appropriate to ask such a detailed question in an Excel Forum such as excelforum.com

Alan


2017-07-03 12:21:29

Sniper

Hi,

I need to modify the code in such a way that ; I will be specifying the work book names which are to be opened " and also the sheet to be copied from them in particular cells ( from where i am running macro).

Also I dont want the sheet to be copied as such in the new workbook, I want the some "range of cells " to be copied to the "active sheet'( again specified range :(


2017-03-17 12:13:05

Bob

Hello - how do I combine first two sheets from several books? (i.e. consolidate/append values from Sheet1/Sheet2 from multiple books in a folder). Thanks


2017-02-16 03:40:15

Ravi

Very Informative and helpful.

I have new request, may be its helps others too.

I am trying to copy the 2 excel into a single excel from different location from the same system.
But I couldn't get the right one.. Can some one help on this please.

Sub GetSheets()
Path = "C:TempAsia"
Filename = Dir(Path & SY.xlsx")

Path = "C:TempEMEA"
Filename2 = Dir(Path & "LN.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


2017-02-02 11:52:05

Andrew Arbogast

I get an error and when I debug, it highlights ChDir sPath


2017-01-31 05:25:30

Tudor

Very explicit and helpful.

I have another request that may also help others.

I am trying to copy a specific cell (same in each workbook) from about 2700 other workbooks into a single excel sheet.

The output I am looking for is:
Workbook Name - Copied Cell

I have tried to design a macro but I just started using it 3 days ago so I can barely understand the coding.

If you could help I would highly appreciate it.

Thank you!


2017-01-23 02:31:54

Durga Prasad

Hi,

I Have found this tool very useful, but would be very happy if you could resolve an issues that is troubling me a lot. Only drawback with this tool is that it can't collect the data that is hidden. i.e., in case if any filter is applied, it collects only the visible data.


2016-10-05 05:50:37

Andrew

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


2016-09-13 20:28:36

Diana

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
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


2016-05-24 05:58:05

Z1234

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.


2016-04-06 13:12:28

Andrew Dykers

If your instructions do not apply to all versions of Excel, please tell us before we have waded through them, and not after.


2016-02-03 07:50:17

Abi

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


2015-09-21 00:50:15

Ramesh

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


2015-07-10 05:07:15

Dinesh Rajah

Hi Allen,

is there a way to use wildcard on the worksheets?

for example Sheet??? pulls through Sheet1 & Sheet2

Thanks Dinesh


2015-06-10 06:11:44

Vijay Gumbar

Hi Allen, Thanks for sharing the add-in. It works just fine.


2015-06-04 11:20:39

Sharon

Was Guilherme's question ever answered? I'm having the same issue. Thanks.


2015-03-24 14:32:46

nick

I AM TRYING TO CREATE A MASTER SPREADSHEET OF NUMBERS DATA FROM THREE SEPARATE WORKBOOKS. I AM A BEGINNER WHAT DO I DO


2015-03-17 16:53:52

baskar

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


2015-03-11 02:05:46

Murthy

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,
Murthy


2015-03-06 14:42:17

Larry Wilchek

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?


2015-01-21 06:54:36

Valendar Turner

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


2014-12-08 15:14:08

Sam

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.


2014-12-08 15:11:49

Sam

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.


2014-12-01 05:49:13

Nida

What if I want to combine the data of different sheets from different workbooks into 1 new sheet of a new workbook


2014-11-20 17:31:26

awyatt

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.

-Allen


2014-11-19 13:44:48

Guilherme

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:umlondonRESEARCHMediabrands AnalyticsClientsITVModellingz MODEL OUTPUTS"
ChDir sPath
sFname = "*"
sFname = Dir(sPath & "" & sFname & ".xlsx", vbNormal)
wSht = "Individual contributions"
Do Until sFname = ""

Set wBk = Workbook.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


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

Thank you,
Guilherme


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.

Newest Tips
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.