How to combine multiple workbooks to one workbook in Excel?

Have you ever been stuck when you have to combine multiple workbooks into a single workbook in Excel? The most terrible thing is that the workbook you need to combine contains multiple worksheets. Can anyone suggest a good way to handle this problem?

Combine multiple workbooks to one workbook with VBA
For advanced users, complicated.

Combine multiple workbooks to one workbook with Move or Copy command
Time-consuming operations.

Combine multiple workbooks to one workbook with Kutools for Excel
Easy, simple. <<Combine Multiple Workbooks>> is just one of 200 powerful features of Kutools    Free Download


arrow blue right bubble Combine multiple workbooks to one workbook with VBA


For the skilled and professional programmers, you can use VBA scripts to combine multiple workbooks into one master workbook. You can deal with this with the following steps:

1. Put all the workbooks that you want to combine into the same directory. See screenshot:

doc-merge-multiple-workbooks1

2. Launch an Excel file that you want to combine other workbooks into.

3. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

Sub GetSheets()
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")
  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

Tip: In the above code, you can change the path to the one that you are using.

4. Then click doc-merge-multiple-workbooks-button button to run the code, and all of the worksheets (including the blank worksheets) within the workbooks have been merged into the master workbook.

Note: This VBA code can merge the entire workbooks into the master workbook, if you want to combine specified worksheets of the workbooks, this code will not work.


arrow blue right bubble Combine multiple workbooks to one workbook with Move or Copy command

If you are a rookie of Microsoft Excel, you have no choice but only have to copy the data of every sheet and paste them in to a new workbook one by one and applying the Move or Copy command. Using the Move or Copy command will help you export or copy one or several worksheets to a new workbook quickly.

1. Open all workbooks that you want to merge into a single workbook.

2. Select all of the worksheet names of a workbook in tab bar. You can select multiple with holding down Ctrl key or Shift key. Right click the worksheet name, and click the Move or Copy from context menu.

doc-merge-multiple-workbooks2

3. In Move or Copy dialog box, select the master workbook that you want to merge other workbooks into from the drop down list of Move selected sheets to book. And then specify the location of the merged worksheets. See screenshots:

doc-merge-multiple-workbooks3 doc-merge-multiple-workbooks-arrow2 doc-merge-multiple-workbooks4

4. Then click OK. The selected worksheets have been moved to the master workbook.

5. Repeat the steps from 2 to 4 to move other workbooks to the master workbook. Then it combines all worksheets of opened workbooks into a single workbook. See screenshots:

doc-merge-multiple-workbooks5
doc-merge-multiple-workbooks-arrow1
doc-merge-multiple-workbooks6

arrow blue right bubble Combine multiple workbooks to one workbook with Kutools for Excel

Fortunately, for rookies and new hands of Microsoft Excel, there are some available tools to help you quickly combine hundreds of workbooks into one. The function Combine in Kutools for Excel makes it much easier to combine multiple Excel files. The application is used for releasing the repetitive and redundant operations in Excel. It actually plays an important role if you have to deal with a large amount of Excel file in your daily work. Now, let's see how to get this function work in combining multiple workbooks.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Read More    Free Download Now

After installing Kutools for Excel, please click Enterprise > Combine.

doc merge multiple workbooks 01

1. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one workbook. See screenshot:

doc merge multiple workbooks 2

2. On step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes, you can click Add button to add other files or folders that you want to combine. Then check the specific worksheets you want to merge from the Worksheets list. See screenshot:

doc merge multiple workbooks 3

3. After specifying the worksheets that you want to merge, then continue to click the Next button, on step 3 of wizard, you can do the following options:

doc merge multiple workbooks 4

Tips

  • (1.) You can rename the new combined worksheets by inserting the workbook names before or after the original worksheets, or you can just keep the original worksheet names by not checking By inserting workbook name option.
  • (2.) If your workbooks contain some blank worksheets, you can keep or skip the blank worksheets as you need by clicking Skip drop down list.

4. Then click Finish button to merge the workbooks, and you will be asked if you want to save this scenario. If you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshots), if you don’t want to save this scenario, please click No. When you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard.

doc merge multiple workbooks 5

5. Then the specified worksheets have been merged into a new workbook, and the worksheets are named with the workbook name before original worksheet name. A new worksheet named Kutools is also generated at the front of all the worksheets at the same time, which lists the workbooks with detailed information of each worksheets. See screenshot:

doc merge multiple workbooks 6

Note: If your worksheet name is too long, maybe the name characters are greater than 31characters, the combined name will not be displayed, and only show the original sheet name.

If you want to know more about this feature, please click Combine multiple worksheets from workbooks into one workbook   
Free Download Kutools for Excel Now.


arrow blue right bubble Related Articles:

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +1 A. Karthi
Hi Plz help me out to this below condition.

I have different workbook which has more than 5 worksheets each in different path. I need to consolidate all worksheets from different workbook into single workbook. Can anyone help me to resolve with macro.TIA!
2013-10-23 05:37 Reply Reply with quote Quote
Permalink +3 Jay Chivo
Quoting A. Karthi:
Hi Plz help me out to this below condition.

I have different workbook which has more than 5 worksheets each in different path. I need to consolidate all worksheets from different workbook into single workbook. Can anyone help me to resolve with macro.TIA!


Please go to download and install the Kutools for Excel, you can quickly get it done. But if you want to use a VBA, it may be too complicated.

For more information about how to get it done, please visit:http://ww w.extendoffice. com/product/kut ools-for-excel/ excel-combine-w orksheets-into- one.html
2013-10-24 06:04 Reply Reply with quote Quote
Permalink +1 Taslima
KUTOOLS Awsome solution.

I need one more help when I create master workbook then cell color of work sheet is changed from original worksheet.

How can I keep it like original worksheet.
2016-07-12 17:54 Reply Reply with quote Quote
Permalink 0 E nic
Our office has duplication of data, (i.e.-name, address, city, amount, date signed) from several excel originals and trying to combine the data is going to be a work in progress. How can that be done to eliminate double work and double information entries?
2017-03-14 18:03 Reply Reply with quote Quote
Permalink +9 Susie
I get a 'runtime error 1004', Copy Method of Worksheet class failed on the line that reads: Sheet.Copy After:=ThisWork book.Sheets(1). I'm using Excel 2010.

Can you assist? Thanks, - Susie
2013-10-31 15:49 Reply Reply with quote Quote
Permalink -1 samuel Birch
Hey Susie,

Ive been working on this problem for a while now myself, getting the same error.

Check to see if the module was created under PERSONAL instead of your active workbook.

Once I created the module under the correct tree, the below code worked just fine.

Sub GetSheets_xls()
Dim Sheet As Worksheet
Path = "C:\Users\yournamehere\Desktop\Testingfolder\"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
Set Sheet = ActiveWorkbook. Sheets(1)
Sheet.Copy After:=ThisWork book.Sheets(1)
'Next Sheet
Workbooks(Filen ame).Close
Filename = Dir()
Loop
End Sub




Hope this helps!
2016-05-24 17:28 Reply Reply with quote Quote
Permalink +1 Chris F
Quoting Susie:
I get a 'runtime error 1004', Copy Method of Worksheet class failed on the line that reads: Sheet.Copy After:=ThisWorkbook.Sheets(1). I'm using Excel 2010.

Can you assist? Thanks, - Susie

Had the same problem, it works when I go to view and unhide "PERSONAL", it seems to have trouble accessing this macro with the master hidden.
2017-02-14 19:23 Reply Reply with quote Quote
Permalink 0 Chris F
Go to view and unhide "PERSONAL" - it seems to have trouble executing the whole code while the master is hidden.
You can make the macro native to that workbook, but you'd have to recreate the whole thing every time you wanted to use it
2017-02-14 19:25 Reply Reply with quote Quote
Permalink +1 Amir
Hi!
Thanks a lot of this file........... ..
:roll:
Best Regard
2013-11-12 11:07 Reply Reply with quote Quote
Permalink +1 Henrik
Hey

Great tip. Did almost all I wanted.

In the combinde woorkbook, I would have lovede for the worksheet name to contain the name of the original woorkbook, so I know which woorkbook the data comes from.

The data I'm combining, is from different archives. I have to search for an entry, but don't know which archive it's in.

So by combining all the data in one file will make it possible for me to search all archives at once.

But I still need to know, which archive the entry is in.

Henrik
2013-12-06 11:32 Reply Reply with quote Quote
Permalink +3 Code
For the code to incorporate the file name just do this.

Sub GetSheets()
Dim temp As String
Path = "C:\Users\....\ Desktop\Excel combine\"
Filename = Dir(Path & "*.xlsx")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
temp = ActiveWorkbook.Name
ActiveSheet.Name = temp
ActiveWorkbook.Sheets(temp).Copy After:=ThisWorkbook.Sheets(1)
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

Note: this is to only copy the first sheet, it can be tweeked to do all sheets
2014-05-13 13:19 Reply Reply with quote Quote
Permalink 0 LAW
How do you incorporate more sheets and how do you specify a different master file to paste all the sheets.
2014-08-19 13:39 Reply Reply with quote Quote
Permalink 0 Adriano Marcato
It's a great solution indeed. thank you.
One Problem though, when I execute it like this excel will ask if I want to save alterations before closing (Since the name was changed), and I don't want to do it for every file (around 32 per execution).
Would there be a way to solve this?
2016-01-19 20:43 Reply Reply with quote Quote
Permalink 0 Tedi
This is superb :lol: helped me a lot....
2013-12-09 09:18 Reply Reply with quote Quote
Permalink 0 Amol
Thanks mate, u made my day from this very helpful website... Actually i was also wanted to combine the same header data of different sheets into 1 master worksheet, the KUTOOL for Excel helped me a lot.... Thank you once again.... :)
2013-12-12 09:06 Reply Reply with quote Quote
Permalink -1 Dinesh
Thanks much for the valuable information. This really works. The steps listed in this article really made my job easier.

Thanks,
Dinesh
2013-12-12 09:58 Reply Reply with quote Quote
Permalink +2 hamid
thanks for sharing your knowledge
2013-12-29 20:40 Reply Reply with quote Quote
Permalink -3 Ness
How do you get it to update the changes from the original workbook? I'm trying to get a national summary which will have each region input their data into their own workbooks and then have the national summary which updates from this? I'd like to have this set up for the full year at the beginning and not be working retrospectively .
2014-01-14 01:18 Reply Reply with quote Quote
Permalink +1 Dado
After combining the worksheets in one Workbook ,how do you save it I couldn't save it is named Book1 and I am clicking on save or save as but not working.any suggestions?
2014-01-16 03:50 Reply Reply with quote Quote
Permalink +4 Sherrill
I followed the steps in "Combine multiple workbooks to one workbook with VBA" and click on "run", nothing happend. I am not aware of errors and not sure how to correct. Would you help me? the following is the code I input in a new workbook.

Thank you

Sub GetSheets()
Path = "p:\download\macro\"
Filename = Dir(Path & "*.xls")
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
2014-01-17 23:05 Reply Reply with quote Quote
Permalink 0 Sherrill
Dear Sir/Madam: I followed the steps of "Combine multiple workbooks to one workbook with VBA" to set up the following module, but nothing happened. Would you help me find the problem? thank you



Sub GetSheets()
Path = "p:\download\macro\"
Filename = Dir(Path & "*.xls")
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
2014-01-17 23:09 Reply Reply with quote Quote
Permalink -3 Dave
for newer versions of excel, try this. I saved my open workbook as catalog, and all the files are in c:\temp.

Sub GetSheets()
Path = "c:\temp\"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
2014-01-27 22:25 Reply Reply with quote Quote
Permalink -3 Dave
Try this for newer versions of excel. I saved my workbook as catalog, all the files are in c:\temp.

Sub GetSheets()
Path = "c:\temp\"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
2014-01-27 22:26 Reply Reply with quote Quote
Permalink 0 jlhall07
Quoting Dave:
Try this for newer versions of excel. I saved my workbook as catalog, all the files are in c:\temp.

Sub GetSheets()
Path = "c:\temp\"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


I keep getting "can't assign to read-only property" concerning the path... Any idea?
2014-03-21 16:13 Reply Reply with quote Quote
Permalink +1 sara
Im having tis problem too. Did you figure it out?
2014-03-27 21:07 Reply Reply with quote Quote
Permalink 0 jlhall07
Nothing yet... Haven't found any solution or had anyone suggest a fix. Sorry...
2014-03-28 12:56 Reply Reply with quote Quote
Permalink 0 Greg
Me too. This was working 6 months ago, the last time I had to run it. Has anyone found the solution yet? If you had it running before and now it won't work could it be something to do with an update by Microsoft? This is really handy tool for my tasks and saves me loads of time. What could have changed that would cause Excel to start displaying this message all of a sudden? Being fairly new to VBA I have little idea where to begin analysing the logic.

Kind regards,

Greg. Glasgow, Scotland.
2014-06-16 11:34 Reply Reply with quote Quote
Permalink 0 fux
It seems that "Path" is now reserved so use just any other name and replace "Path", e.g. "Mypath".
2015-07-08 15:21 Reply Reply with quote Quote
Permalink 0 Ma Hi
Maybe you should change
ReadOnly:=True
to
ReadOnly:=False,
I have done and it was helpful
2017-03-26 07:19 Reply Reply with quote Quote
Permalink 0 Patrick
I was looking for something along these lines but wanted to comment. Doesn't the Do While Filename "" need to be something other than ""? or am I reading that wrong?

Maybe Do While NOT filename = ""

Just a thought...
2014-04-11 18:13 Reply Reply with quote Quote
Permalink 0 Anand Darbha
I want to combine all the sheets into one sheet where the headings are common...pls help
2014-01-28 12:45 Reply Reply with quote Quote
Permalink -7 Sanora
Once the movie was shot, I would put the scene into play.
Later on you can then move towards parts that you want to service that you choose aren't also far
away from dwelling. With such a high quantity and diverse range of
companies offering employment in this Sector such as Hospitality, Retail, Public Health, Media, and Financial Services, students at colleges in London will have direct access to an assortment of job vacancies.


Review my website: escorts london} (Sanora)
2014-02-27 12:13 Reply Reply with quote Quote
Permalink 0 Nazeer
Hi,

I tried using the above macros to collate few files, unfortunately no results...

can some one help me getting rid of manually collating files.
2014-05-04 05:23 Reply Reply with quote Quote
Permalink 0 christian
i have 112 excel sheets i want to put into a single sheet without copy and paste. Please help me out.
2014-06-12 11:20 Reply Reply with quote Quote
Permalink 0 Dileep
I Have a workbook it contain around 250 Sheet . I need to Cobain in one sheet. please give me a solution
2014-06-20 12:09 Reply Reply with quote Quote
Permalink +5 Ginger41
Try this out...I got this from another site but unfortunately I can't remember the lady's name so my apologies for not giving her a mention, my bad"

Combine multiple WB's in Excel:
REMEMBER TO CHANGE MyPath = !

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.Dis playAlerts = False
Application.Ena bleEvents = False
Application.Scr eenUpdating = False
MyPath = "\\MyPath\etc\e tc..."
Set wbDst = Workbooks.Add(x lWBATWorksheet)
strFilename = Dir(MyPath & "\*.xls", vbNormal)

If Len(strFilename ) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open( Filename:=MyPat h & "\" & strFilename)

Set wsSrc = wbSrc.Worksheet s(1)

wsSrc.Copy After:=wbDst.Wo rksheets(wbDst. Worksheets.Coun t)

wbSrc.Close False

strFilename = Dir()

Loop
wbDst.Worksheet s(1).Delete

Application.Dis playAlerts = True
Application.Ena bleEvents = True
Application.Scr eenUpdating = True

End Sub
2014-06-23 10:47 Reply Reply with quote Quote
Permalink +4 Niki Foster
None of these worked for me

I finally got this one to work. FYI I am using 2010

'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer

RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

ThisWB = ActiveWorkbook. Name

path = "mypath....." ' Dont't forget to change this

Application.Ena bleEvents = False
Application.Scr eenUpdating = False

Set shtDest = ActiveWorkbook. Sheets(1)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open( Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).R ange(Cells(Rowo fCopySheet, 1), Cells(ActiveShe et.UsedRange.Ro ws.Count, ActiveSheet.Use dRange.Columns. Count))
Set Dest = shtDest.Range(" A" & shtDest.UsedRan ge.SpecialCells (xlCellTypeLast Cell).Row + 1)
CopyRng.Copy Dest
Wkb.Close False
End If

Filename = Dir()
Loop

Range("A1").Sel ect

Application.Ena bleEvents = True
Application.Scr eenUpdating = True

MsgBox "Done!"
End Sub
2014-06-25 21:44 Reply Reply with quote Quote
Permalink 0 michelle
How do I edit this so that the data pulled in always starts on the top row? If I run this code twice, it adds the data to the end of my previous data (from the first run of the macro).
2014-07-23 18:01 Reply Reply with quote Quote
Permalink -1 orivera
Change this line:

RowofCopySheet = 2

to

RowofCopySheet = 1
2014-08-21 15:11 Reply Reply with quote Quote
Permalink 0 Mohideen
HI , I have multiple Excel File (single sheet) different folder with password protection. i want end of the day combine all data to one Master file. Every time I have to Enter password and open the file and copy paste to master file.. Kindly help me with VBA code for this please.
2017-01-29 19:43 Reply Reply with quote Quote
Permalink 0 khar booza
This is very hard, is it possible you can do it for me?
2014-07-18 12:57 Reply Reply with quote Quote
Permalink 0 Aren
I use an app rather than code and recode. Data Amalgamation by pasync
2014-07-20 01:52 Reply Reply with quote Quote
Permalink 0 Dr. Paul B.
That code was great! Thanks so much.
2014-08-07 00:23 Reply Reply with quote Quote
Permalink +1 santhosh.s
:lol: VERY GOOD SHORT CUT IN EXCEL MY DEAR PEPOLE
2014-08-22 10:24 Reply Reply with quote Quote
Permalink 0 John
Excel 2010. Script as is did work, but I do not have the 'webbrowser-lik e tabs" at the top of the newly combined workbook as illustrated at the top of the page. Is the illustration just a well placed ad???
2014-09-15 19:37 Reply Reply with quote Quote
Permalink 0 Aidan
Hi,
I want to setup my a master set of excel documents in a folder and give them all the files names which will be numbers only (eg 0001, 0002, 0003 and so on. Information contained within them will be feeding a master document also within the same folder. I have to set up all the links at the start so that i can copy the entire folder and reuse on the next project. So its basically a simply link, problem is I want to allow the feeder documents name to change slightly throughout the project. So feeder document 0001 may become '0001 Job12', 0002 might become '0002 roadworks' and so on. I can manually update the links but there will be 100 feeder excel documents, each containing loads of links. Can I maintain the link, even if i change the document name slightly ?
2014-10-28 11:13 Reply Reply with quote Quote
Permalink 0 Trease
I need to combine six spreadsheets into one. Some workbooks have multiple tabs and even though some of the info is the same, all is not. I need to turn it all into one Spreadsheet and all like categories added together for a final sum. Can this help me?
2014-11-11 04:36 Reply Reply with quote Quote
Permalink 0 Shady39
Thanks Ginger41!!!!

Works perfectly. I'm using Excel 2010.

Only issue I had was when I copied Ginger41's sub, it for some reason put gaps in weird places. Just read through and tidy up any gaps (or run the Debug and it will pick them up).
2014-11-12 01:02 Reply Reply with quote Quote
Permalink +1 Purnendu Biswas
I have a work book, which have 256 sheets.
how to collate the sheets.
File name- "My Codes"
Sheets name- Table 1 to Table 256
2014-11-12 09:33 Reply Reply with quote Quote
Permalink +1 swattie
My code works great but only if it is pulling from files saved to the computer. Will it not work if the files I am merging are on a shared drive? I appreciate the help!
2014-12-05 02:14 Reply Reply with quote Quote
Permalink 0 Karan kumar
Woooowww... this is a great software for me !!!

My all work is done
thanks a lot for this
2015-01-28 09:08 Reply Reply with quote Quote
Permalink 0 nEELAM SHARMA
pls thanks for your guidelines
2015-03-05 09:58 Reply Reply with quote Quote
Permalink +1 Joanne K
Trying to use Kutools to combine multiple workbooks that contain graphs, and for some reason the sheets with graphs do not get included. Please advise.
2015-06-08 14:12 Reply Reply with quote Quote
Permalink 0 arun kumar
hi i am using ur coding its show error
2015-06-08 22:29 Reply Reply with quote Quote
Permalink -1 vijay
thanks for script, i would like to like to get file name adjacent to data ie source file for each data. can someone modify script suitably.
2015-06-16 11:30 Reply Reply with quote Quote
Permalink 0 imran
Is this possible to link multiple sheet in one master sheet .eg if we need to extract data in one single sheet without going in to multiple sheet !
2015-06-18 08:40 Reply Reply with quote Quote
Permalink 0 Sachin
I need to consolidate data from Sheet1 of multiple workbooks into Sheet1 of a separate workbook. Please help. (All the workbooks contain similar set of data with headers)
2015-06-26 05:59 Reply Reply with quote Quote
Permalink 0 Snabo
Excellent VBA code. Thanks
2015-06-27 21:12 Reply Reply with quote Quote
Permalink 0 Chris
I can't get the following to work... Any help is much appreciated.

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "\\MyPath\etc\etc..."
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(C \ Users \ Chris \ Desktop \ Planet Soccer \ Reports And Buying \ UPCS)

If Len(strFilename ) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open( Filename:=CUser s \ Chris \ Desktop \ PlanetSoccer \ Reports And Buying \ UPCS)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Loop
wbDst.Worksheets(1).Delete

Application.Dis playAlerts = True
Application.Ena bleEvents = True
Application.Scr eenUpdating = True

End Sub
2015-07-25 22:50 Reply Reply with quote Quote
Permalink 0 Greg
Hello,
I see many people interested thoughts..

Maybe someone could help me with similar problem, please?!

I need to take 1 specific sheet from a files stored within 1 folder, (all those files have got the same sheet name) - and place that sheets into the new workbook. Anyone has done something like this? I know that I need to use Copy or Move - and the Loop within the folder - its similar like this but I can't find solution...
2015-07-30 14:14 Reply Reply with quote Quote
Permalink 0 Sourabh
Hi !!
I need help...
I want to split the data from one excel to different excels.
having the data of national level and split the same in region wise in seperate excel along with the all sheet that i have in my master data.
Please help...
2015-08-19 10:57 Reply Reply with quote Quote
Permalink 0 krishna sharma
i have 3 excel work book and each work book has 31 sheet. i want to make one work book with 31 sheet . it is necessary that all sheet of work book must be add.
2015-09-07 10:24 Reply Reply with quote Quote
Permalink 0 Samuel
I am having data for all the 365 days for 14 year in one excel sheet.

Now we want to separate the data year wise (Ex. one excel file for year 2002 in that 12 Sheet ie for 12 month and another excel file for year 2003 in that 12 Sheet ie for 12 month).

We are able to separate month wise data (by using Excel Kutools - Split Data) and it will create excel sheet for each month (ie it will create totally 24 sheets) and then we should Split to Workbook and it will be 24 excel file and again we should combine 12 file for each year. This is taking lot of time.

Now our problem is while splitting the date we want Excel to create Year wise Excel files and in each excel file 12 sheets for 12 months. Can we do this in same time.

Please tell us because we are having lots of data and it is taking lot of time.
2015-09-26 06:54 Reply Reply with quote Quote
Permalink 0 DaisyD
Thanks for this. Only your website gave me the solution for what I was looking for. Very helpful and correct.
2015-10-29 06:00 Reply Reply with quote Quote
Permalink +5 RichardHead
Make sure you add a \ at the end of your path.

Example:

Bad: Path = "C:\Users\dt\De sktop\dt kte"

Good: Path = "C:\Users\dt\De sktop\dt kte\"
2016-02-15 15:40 Reply Reply with quote Quote
Permalink +4 Zadius
Quoting RichardHead:
Make sure you add a \ at the end of your path.

Example:

Bad: Path = "C:\Users\dt\Desktop\dt kte"

Good: Path = "C:\Users\dt\Desktop\dt kte\"


This worked for the people saying nothing happens!!! Thanks soooo much!
2016-02-23 17:55 Reply Reply with quote Quote
Permalink +1 Summer
I love playing around with VBA. It's like magic - Poof! and everything is done for you. I've been in programming for over 10 years but I still enjoy the sight :D
2016-03-30 05:21 Reply Reply with quote Quote
Permalink 0 Shubham
GREAT STUFF!! You saved me a lot of effort!
2016-04-11 14:04 Reply Reply with quote Quote
Permalink 0 Gaurav Sethi
Hi,

My name is Gaurav, I have 10 Excel workbook & i want to add all file in one workbook. Please suggest.
2016-05-10 05:18 Reply Reply with quote Quote
Permalink 0 wooly
I'm new to Excel and am struggling to identify what I need to personalise in this code to make it work. Obviously "path" but is that it? as I'm getting errors on the two "set copyrng" and "set dest" row.

None of these worked for me

I finally got this one to work. FYI I am using 2010

'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer

RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

ThisWB = ActiveWorkbook. Name

path = "mypath....." ' Dont't forget to change this

Application.Ena bleEvents = False
Application.Scr eenUpdating = False

Set shtDest = ActiveWorkbook. Sheets(1)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open( Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).R ange(Cells(Rowo fCopySheet, 1), Cells(ActiveShe et.UsedRange.Ro ws.Count, ActiveSheet.Use dRange.Columns. Count))
Set Dest = shtDest.Range(" A" & shtDest.UsedRan ge.SpecialCells (xlCellTypeLast Cell).Row + 1)
CopyRng.Copy Dest
Wkb.Close False
End If

Filename = Dir()
Loop

Range("A1").Sel ect

Application.Ena bleEvents = True
Application.Scr eenUpdating = True

MsgBox "Done!"
End Sub
2016-06-15 14:45 Reply Reply with quote Quote
Permalink 0 Hoang
The above code works well, but it seems that it doesn't follow any certain order, in my folder, each excel file ( before merging ) has 1 sheet with same name,for exp: book1 contains sheet named A, book2 also contains sheet named A ,....then after merging by VBA, the combined workbook contains sheets named A, A(1), A(2), A(3),...in random order, A(1) actually doesn't belongs to initial book1. So it's hard for me to identify, I can't know each sheet originally belongs to which file. The result I expect is that all sheets will be combine in name order of original separated file

Perhaps i can't explain my point well, if someone has same situation or can understand me, then pls kindly upgrade vba code that can combine sheets in order of alphabet name or ascending number ?
2016-07-13 16:15 Reply Reply with quote Quote
Permalink 0 Gnanesh
Thank you very much for the script for combining workbooks!
2016-08-02 11:42 Reply Reply with quote Quote
Permalink 0 MUHAMMAD MURTAZA
ASSALAM O ALIKUM,,

LOT OF THANKS FOR COMBINE WORK SHEET IN MASTER SHEET
2016-08-12 12:44 Reply Reply with quote Quote
Permalink 0 cindy
thanks for the macro guided for 'Combine multiple workbooks to one workbook with VBA'.

However, i do have an additional cirtumtances.
Let's take your example.
In the folder 'dt kte', there are 4 workbook (book1, book2, book3 & book4)
The macro will merge all worksheet in every single workbook into my excel file.
However, the circumtances i need is:-
in every workbook, there is mutual sheet named "HIJ".

what would be the macro if i only want to merge the HIJ worksheet in book1 to book4, into one workbook i want?
2016-08-15 08:05 Reply Reply with quote Quote
Permalink 0 Ashwath
Hello, I tried this code for combining the files and it worked well. However I have a situation where in I have to combine the data from different sheets of multiple files in to one master file with multiple sheets.

For ex: I have 10 different file and each file has data in Sheet 1 and Sheet 2. I want to create a consolidate data in to one master and all data from sheet 1 of multiple files should be consolidated in one sheet 1 of master and sheet 2 in sheet of master. Can this be done?
2016-09-05 15:10 Reply Reply with quote Quote
Permalink 0 wackiemark
hello can anyone please help me with this problems in different codes.

1.) copy all sheets in selected workbooks in a folder and paste it to current workbook. and if the worksheets have same sheetname, it will add a number e.g (1) on the side of the sheetname.

2.) copy specific worksheet in selected workbooks and paste to current workbook, and if the worksheets have same sheetname, it will add a number e.g (1) on the side of the sheetname.

thank you in advance
2016-09-21 02:32 Reply Reply with quote Quote
Permalink 0 Rakesh G.B.Groups
i want to mix or update a excel file by a old file data and new file data
please suggest me command

Thank you
2016-09-26 09:12 Reply Reply with quote Quote
Permalink 0 BWANG
Thanks. With some changes, the following works for me:

Sub GetSheets() 'make sure the module is created in the current Excel spreadsheet "Weather data 201611.xlsx", not the "Personal.xlsb"
Path = "C:\Weather Data\201611\" 'remember to change the file location here
Filename = Dir(Path & "*.csv") ' .csv is the type of file while we wanted to open, change to xls or xlsx if required
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=False 'Depending on the files which we want to open, if it is a read only file then change to ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbook s("Weather data 201611.xlsx").S heets(1) 'Remember to change the file name to the file name while is new open for this module. Make sure the type match as well
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
2016-11-09 01:40 Reply Reply with quote Quote
Permalink 0 pat
I followed the instructions but when I Run a screen opens that lists Macros. I select GetSheets and Run but nothing happens.

Sub GetSheets()
Path = "G:\COM\Diabete s Center\Pat\Time Sheets\My time Sheet 2013"
Filename = Dir(Path & "*.xlsx")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook. Sheets
Sheet.Copy After:=ThisWork book.Sheets(1)
Next Sheet
Workbooks(Filen ame).Close
Filename = Dir()
Loop
End Sub
2016-12-01 15:35 Reply Reply with quote Quote
Permalink 0 kannan
THANKS FOR THIS VALUABLE FORMULA....
2016-12-06 04:54 Reply Reply with quote Quote
Permalink 0 Mohideen
HI , I have multiple Excel File (single sheet) different folder with password protection. i want end of the day combine all data to one Master file. Every time I have to Enter password and open the file and copy paste to master file.. Kindly help me with VBA code for this please.Quoting kannan:
THANKS FOR THIS VALUABLE FORMULA....
2017-01-29 19:43 Reply Reply with quote Quote
Permalink 0 Mohideen
HI , I have multiple Excel File (single sheet) different folder with password protection. i want end of the day combine all data to one Master file. Every time I have to Enter password and open the file and copy paste to master file.. Kindly help me with VBA code for this please.
2017-01-29 19:41 Reply Reply with quote Quote
Permalink 0 Jan Drozd
Recently we we're solving this task for customer with more than 2000 different information types, hundreds of source excel files. They wanted to merge them into one big sheet to wok with.

As a part of this work, we've developer Excel Stats Merger app: https://www.jandrozd.eu/products/excel-stats-merger/ - it does not require MS Office to do the job. You simply define merging rules and then process your files.
2017-02-13 01:13 Reply Reply with quote Quote
Permalink 0 E nic
Several excel files to merge using access?
There are several files on the network.
2017-03-15 23:55 Reply Reply with quote Quote
Permalink 0 Ma Hi
Thanks to every one,I have tried this program and it was helpful, I had 30 excel files and I wanted to merge them in "bahmann.xlsx".
Sub GetSheets()
Path = "C:\Users\16262 \Desktop\bahman \"--- you have to put "\" at the end of your path
Filename = Dir(Path & "*.xlsx")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("bahmann.xlsx").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
2017-03-26 07:24 Reply Reply with quote Quote
Permalink 0 Kevin Coutts
THE FOLLOWING CODE WORKED FOR ME IN EXCEL 2016. YOU NEED TO SPECIFY YOUR OWN DIRECTORY IN PLACE OF THE ONE I USED. IN MY CASE THE WERE REQUIRED IN THIS LINE (CONTRARY TO WHAT SOME OTHERS USED ABOVE): Do While Filename "".

THE CODE THAT WORKED FOR ME FOLLOWS (I EMPLOYED THE STEPS OUTLINED IN THE ORIGINAL POST TO CREATE THIS CODE):


Sub GetSheets()
Path = "C:\Users\Kevin \Documents\Comb ine Excel Workbooks\Workb ooks to Combine\"
Filename = Dir(Path & "*.xlsx")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
For Each Sheet In ActiveWorkbook. Sheets
Sheet.Copy After:=ThisWork book.Sheets(1)
Next Sheet
Workbooks(Filen ame).Close
Filename = Dir()
Loop
End Sub
2017-04-15 19:16 Reply Reply with quote Quote
Permalink 0 Johnny Bravo
When I click Finish for Combine Worksheets step 3 of 3,
it asks me to save a file name,
and then it just sits there.
2017-04-19 19:41 Reply Reply with quote Quote

Add comment


Security code
Refresh