Combining Worksheets from Many Workbooks

by Allen Wyatt
(last updated April 4, 2019)

60

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Defining Default Printers on a Document Level

If you use multiple printers, you may wonder how to set each document in Word to remember which printer to use for that ...

Discover More

Calculating Months for Billing Purposes

Different businesses have different ways to calculate elapsed time for billing purposes. Figuring out a formula that ...

Discover More

Moving Drawing Objects

When you need to move a drawing object around your document, you use the mouse after you select the object. This tip ...

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)

Moving from Sheet to Sheet

Need to move quickly through the worksheets in a workbook? Learn the keyboard shortcuts and you can make short work of ...

Discover More

Renaming Worksheets

Some easy steps to rename the worksheets in your Excel workbook.

Discover More

Creating a Copy without Formulas

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

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 6Mpixels. 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 seven more than 7?

2019-10-21 03:20:50

vinay

Hi all,
i need vba to merge multiple sheets data in one excel with same sheets [data should be merged accordingly with same sheets]
Please assist

Thank you


2019-10-15 08:07:21

OviSele

It works perfect! Thank you very much!


2019-04-29 07:20:24

Louise

Thank you Alan


2019-04-29 06:52:57

Keith

I would like to know how to:

Take a list of addresses in excel, export this using mail merge function to word
Then have google maps read the address and insert the map automatically into the word doc

I can use the mail merge function fine but having trouble inserting the map.

If you have any suggestions pls let me know


2019-04-27 05:44:14

Alan Elston

( Louise,
my suggeston was intended fo Allen's last routine, Sub CombineSheets() )


2019-04-27 05:41:02

Alan Elston

Hello Louise,
It is very easy to do what you want using Allen Wyatt’s routine, since the name of the file from which the worksheet comes is in the variable sFname. (This is because Dir( ) returns the file name, ( if it finds one) )
So try any of these code lines, or variations of one of them to suit you.
You can add the line just after this line:
Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
Just after that code line, the copied worksheet will be active. So the following lines should do something like you want.
The first three put the file name in the 4th 5th and 6th cell in the worksheet. The last line will add part of the file name to the worksheet name.
Let ActiveSheet.Range("D1").Value = sFname
Let ActiveSheet.Range("E1").Value = "This came from file " & sFname
Let ActiveSheet.Range("F1").Value = "This came from file:" & vbLf & sFname
Let ActiveSheet.Name = ActiveSheet.Name & "_" & Left(sFname, 4)

Alan Elston


2019-04-26 10:53:38

Louise

Hello Allen

Thank you for this. I only understand Excel basics at the moment so this was really useful. Is it possible to make the macro pick up the name of each workbook it copies from so that I can identify where each tab has come from?

Thanks


2019-04-12 08:47:32

Alan Elston

Hello TASNEEM,
_1 ”…macro enabled!!!! Is that an issue?...”
No this should not be an issue. Because, this part, ".xl*" , will allow like ".xls" , ".xlsm*" , ".xlsx*" , ".xlsb*"

_2 “…. it comes down to Debug. Please advice! …..”
_2a) There can be hundreds of reasons. We cannot advise unless you give us much more details and information of your problem.
_2b) One Advice help can be… If you mean that it errors and gives the debug window: ?
In the VBE ( Alt+F11 )
Do you have error , Error.jpg; h t t p s ://imgur.com/jIpz6wf
Or, do you have Error and Debug.jpg; h t t p s ://imgur.com/KbW2Fi0
We want Error and Debug Debug.jpg ; h t t p s ://imgur.com/yCtwR1V
( This may need to change these 3 Options
Tools Options.jpg ; h t t p s ://imgur.com/BlScZbm
Options General.jpg ; h t t p s ://imgur.com/HECXLjh
Error trapping 3 Options.jpg ; h t t p s ://imgur.com/7f8C3Ue )
So now where is error? Where is Yellow Error.jpg ; h t t p s ://imgur.com/hFLVSb5

What is error ?

Alan Elston

Ref
h t t p : / / w w w.eileenslounge.com/viewtopic.php?f=30&t=31608#p244699
h t t p : / / w w w.eileenslounge.com/viewtopic.php?f=27&t=16407&p=247121#p247121


2019-04-11 05:38:24

TASNEEM

Helo Alen.

I have to copy first sheet of 42 workbooks that I have under one folder into one master file, but as separate sheets. I have used your code but it comes down to Debug. Please advice! I have almost tried every code that I could trace.
All my 42 workbooks are macro enabled!!!! Is that an issue?


2019-02-04 09:32:39

luke

Good morning Doc Elstein,

I really couldnt figure out why this kept looping, there was only one workbook open with the names IRS, TRS, CDS...and i only went to using that error handler message out of necessity...i was able to complete the project using that workaround and im happy with it...in the future i will definitely seek you out concerning new codes.

Thank you for the advise and input
Luke


2019-02-02 05:43:24

Alan Elston

Hi Luke,
I am still puzzled at the error.
I am assuming that all your workbooks contain 3 sheets with the names "TRS" , "IRS" , "CDS" ?
As I said, The most likely cause of that error is that VBA has failed to found a worksheet with the name "IRS"
The fact that you say …“I am actually able to pull in the "IRS" tab, however the code keeps looping where running thru that same "IRS"code line by line the code and will not continue to further script” … is suggesting to me that maybe not all your workbooks have a worksheet with a name "IRS" in it??

If you are happy with you workaround, then that is fine. But generally it is regarded as very bad practice to use that sort of Error handler. It is especially bad practice in your requirement because there is no reason why you should not be able to do what you want to do without error handling. My advice would be to get your routines to work without error handling. But that is your decision. I expect there is something very simple that one of us is overlooking.

I am Alan Elston, By the way, not Allen Wyatt who is the owner of this Blog site and author of the original code.. The comment section here is not so well suited to passing long codes, as you may have noticed. It is mainly intended for short comments.
Allen Wyatt did say that I could pass people with longer questions on to Excel forums where it is easier to answer questions like yours.
Currently I am spending some time here:
excelfox.com/forum/forumdisplay.php/2-Excel-Help
If you are not in a major rush I will be happy to help you there develop, what should be, a very simple solution to your problem. It is easier there to exchange coding and test files.

Otherwise, good luck with your project
Alan Elston ( DocAElstein )


2019-02-01 14:38:43

luke

Hey Allen,

Thanks again for the script...with the issue that i was having i constructed this workaround using a master macro and using "on error resume next"....

Option Explicit

Sub Master()

Dim wkb As Workbook
Dim sWksName As String

Call CopySheets1
Call CopySheets2
Call copysheets3

End Sub


Sub CopySheets1()

Dim wkb As Workbook
Dim sWksName As String

On Error Resume Next

If Err.Number <> 0 Then
Exit Sub
End If

sWksName = "IRS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Worksheets(1)
End If
Next



End Sub

Sub CopySheets2()

Dim wkb As Workbook
Dim sWksName As String

On Error Resume Next

If Err.Number <> 0 Then
Exit Sub
End If

sWksName = "TRS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1)

End If
Next
End Sub

Sub copysheets3()

Dim wkb As Workbook
Dim sWksName As String

On Error Resume Next

If Err.Number <> 0 Then
Exit Sub
End If

sWksName = "CDS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1)

End If
Next

End Sub


2019-02-01 09:29:46

Luke

Hey Allen,

Thank you very much for the quick turnaround and thanks for the feedback..i do understand that underscore is simply a way to make code "neater" and that this code only works for open workbooks.

I am actually able to pull in the "IRS" tab, however the code keeps looping where running thru that same "IRS"code line by line the code and will not continue to further script....i am simply looking for the code to continue to further and bring in more tabs on workbooks that are open...

Also i have tried submitting mutually exclusive macros to pull in seperate worksheets using a master macro and call functions and the code still continually loops and errors out to a type 9 and will not continue past the first "IRS" string...

any ideas? Please advise...

When im using the master macro and call function the script looks like this

Option Explicit

Sub Master()

Call CopySheets1
Call CopySheets2
Call copysheets3


End Sub


Sub CopySheets1()
Dim wkb As Workbook
Dim sWksName As String

sWksName = "IRS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Worksheets(1)

End If
Next


End Sub

Sub CopySheets2()
Dim wkb As Workbook
Dim sWksName As String

sWksName = "TRS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1)

End If
Next
End Sub

Sub copysheets3()

sWksName = "CDS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1)

End If
Next

End Sub


2019-02-01 03:07:02

Alan Elston

Hi Luke,
I can only make a few guesses.
First: ..
Note that this
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1)
is a single code line. You can just as well write it as
wkb.Worksheets(sWksName).Copy Before:=ThisWorkbook.Sheets(1)
The use of the _ is just for convenience , or rather personal preference, as some people like to spread out the text of a single code line over more than one line . The _ allows you to split up a code line to display it for you over more than one line. But VBA just joins it all together again and VBA “sees” it just as a single line, regardless of whether you have split it with one or more _’s or not..

Second:
At that code line point you have not yet „pulled in the "IRS" tab “. All that point all you have done is “filled” a string variable with its name. You can fill any string variable with almost any name at any time in a code. Like: If you have a bucket and fill it with sand or water or even something disgusting then it effects no one or anything until you use it somewhere. You tried to use your variable to find a worksheet with the name that you had in it. That is where the problem came

90.% of the time when such a code line gives you the error “type 9 "subscript out of range" error “ it is because the Workbook, wkb , does not have a tab with the Name you are trying to access it by, in your case "IRS"
The code line that errors is the first time that you try to do anything with the tab "IRS". But you did not manage to get hold of any tab with that name. VBA errored when it tried to get at a worksheet with that name

Because your code errored at that point, your code has not done anything yet with any worksheet.

Alan Elston


2019-01-31 16:37:45

Luke

Hey Allen,

Thank you for the great script....I am trying to draw in worksheets from open workbooks, then creating a "master" workbook.

I keep recieving a type 9 "subscript out of range" error...for whatever reason the script will pull in the "IRS" tab but the macro will not continue onto "TRS"....any ideas? I am simply trying to pull in these three files...

here is the code i am using....thanks again for all the insight

Sub CopySheets1()
Dim wkb As Workbook
Dim sWksName As String

sWksName = "IRS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1) code is breaking here with subscript out of range error
End If
Next

sWksName = "TRS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1)

End If
Next

sWksName = "CDS"
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(sWksName).Copy _
Before:=ThisWorkbook.Sheets(1)

End If
Next

End Sub


2018-09-17 06:45:26

Mamtha

Hi . This works great. But can i know how to combine/consolidate the data from different workbooks (specific sheet name) into one work sheet instead of different workbooks?? If i can select the range of data in the sheet that need to be consolidated? Please help


2018-08-30 18:31:20

Puran

I am trying to combine Sheet1 on every Excel file in the folder and the file names are SO 56777.XLSX etc., so I put *SO* and am getting a Run-Time error9


2018-05-14 22:21:20

nancy

now it's not working again!


2018-05-14 22:12:09

Nancy

3507_2018.xlsm is the filename. I tried changing it to alphanumeric name and it worked fine. Does the program have difficulty reading it if the file name is numeric in pattern?


2018-05-14 05:15:38

Nancy

Hi,

I am getting this error when I run it:

Run-time error '1004':
Sorry, we couldn't find 3507_2018.xlsm. Is it possible it was moved, renamed or deleted?

I copied the following:

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

I got an error on:
Set wBk=Workbooks.Open(sFname)

It used to work but when I tried it again, I keep on getting this error. Help me please.

Thanks,
Nancy


2018-03-22 03:48:59

Alan Elston

Hi geegeewhy

I am assuming you want to bring all your data into a main Workbook, and as you say, “…all Sheet1 of all workbooks will be combined, all Sheet2 of all workbooks will be combined…”
As you suggest it would not be useful to copy an entire worksheet, as done in this Blog , as it is not easy to paste sheets “on top of each other” as it were

So you would need to copy data ranges rather than an entire worksheet. Those ranges would then be brought together in a single worksheet

There are many ways to do that, many ways to copy a data range and many ways to combine this information from severely worksheets into one

It is not possible to give a general code to do that such as the one given by Allen Wyatt for copying the entire worksheets. This is because one would need to know
_how your data is organised and also
_how you want the data to be combined, that is to say one would need to know how each final sheet containing all the data should look like.

Alan Elston


2018-03-21 06:01:00

geegeewhy

How would you combine the workbooks where each sheet is combined within its own sheet name?

that is all Sheet1 of all all workbooks will be combined, all Sheet2 of all workbooks will be combined.

To combine it as separate worksheets isnt useful


2018-03-13 11:35:32

Alan Elston

Testing for Notifications from this site:
Alan Elston


2018-03-13 11:34:28

Alan Elston

Testing for Notifications from this site:
Alan Elston


2018-03-13 11:31:12

Alan Elston

Testing for Notifications from this site:
Alan Elston


2018-02-07 02:58:46

Alan Elston

Hi Krishna Priya,It is difficult in the confines of a Comment section to give you all the help that you may need on this.But, a couple of things I notice immediately._A) You have not followed my notes _1) from previous: You are still closing your workbook before the Paste. ( I suspect then that formats are not therefore available in the clipboard for any following Paste. See my previous comments. )_B) In the line which is erroring your syntax is completely mixed up.You are attempting in that code line to make some strange combination of two different things.These two different things are:_B)(i) Worksheets .Paste Method : https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-paste-method-excel_B)(ii) Range.PasteSpecial Method : https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-pastespecial-method-excel I think you will require ONLY the Range.PasteSpecial Method for your requirement.But without some sample test data it is difficult to help you further in the confines of this comment section.If you require more detailed help to get your code working then I suggest you ask in a Forum such as here:http://www.excelfox.com/forum/forumdisplay.php/2-Excel-HelpThere you could upload a couple of sample files (preferably with a reduced size of desensitized data ).You would have more space there to fully explain your requirement.Alan Elston


2018-02-06 08:12:37

Krishna Priya

Thanks Elan For your input. I tried using the range method. but i'm getting the runtime error 424. Can you please help me .
Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "Paste your path \"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
objRange = objWorkbook.Worksheets("Sheet1").Range("A2:G2").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 7)).PasteSpecial.objRange -error here (424 )


MyFile = Dir
Loop
End Sub


2018-02-06 02:46:43

Alan Elston

Hi Krishna Priya,

In some situations .Paste may give you the formats. Sometimes it may not. It all depends which Clipboard may be used and which version of any copies that it holds may be used. Other factors may also effect this.
It has been my experience that Formats are usually only preserved in a paste if the Workbook from which the copy was made is still accessible to Excel. Possibly this means that the Excel Clipboard is used in this case. I am not sure.
You have closed the Workbook after, the .Copy, which will have some effect on how a .Paste works. I am not sure of the exact effect, but I expect all or most formats may no longer be available after the workbook is closed.
You will need, I expect, to do one or possibly two things.
_1) Do not close the workbook from which you have copied until after the Paste.
_2) You may also need to use the Range .PasteSpecial Method rather than the Worksheets .Paste Method. ( Your current code uses the Worksheets .Paste Method )

Alan


2018-02-05 07:40:23

Krishna Priya

Hi, I'm using the code below: It worked for me in excel 2013 to combine multiple workbooks first sheet to one master workbook but only difficulty which i'm facing is that the exact format is not pasting i.e.plain text is getting copied instead of rich text . The background color of the cell and font is not being pasted. In plain English only Paste is working and not paste special .Can someone help me ?

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "Paste your path \"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
Range("A2:G2").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 7))

MyFile = Dir
Loop
End Sub


2018-01-25 02:52:54

Alan Elston

Hi Kass
What exctly is the path that you have entered?
Are you sure that it exists?
It must be the full path and shown correctly.

Alan Elston

P.s.
It might be easier to
_1) include this function in your code module

Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function


_2) Then replace the code line
sPath = InputBox("Enter a full path to workbooks")
with
sPath = GetFolder

This will allow you to select the Folder. That way it is easier to get the correct full path syntax
https://www.excelforum.com/excel-programming-vba-macros/1215030-choose-folder-for-sheet-import.html


2018-01-24 09:59:06

Kass

HI,

I have the same issue as Tracy E I have C: at the beginning of my path. I get run time error 76 can anyone help fix -


2017-12-13 04:17:24

Alan

@ Tracy E
What does your given full string path look like?
Does it include the directory?
it must start with something to indicate the directory. The start should look like
C:
or
H:
or
F:

... etc
Alan


2017-12-12 10:48:00

Tracy E

Did Andrew's issue get answered? I'm having the same issue:

"Andrew Arbogast 2017-02-02 11:52:05

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



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.