Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

How to combine multiple workbooks into one master workbook in Excel?

Have you ever been stuck when you have to combine multiple workbooks into a master workbook in Excel? The most terrible thing is that the workbooks you need to combine contain multiple worksheets. And how to combine only the specified worksheets of multiple workbooks into one workbook? This tutorial demonstrates several useful methods to help you solve the problem steps by steps.


Combine multiple workbooks Into one workbook with Move or Copy function

If there are just a couple of workbooks need to be combined, you can use the Move or Copy command to manually move or copy worksheets from the original workbook to the master workbook.

1. Open the workbooks which you will merge into a master workbook.

2. Select the worksheets in the original workbook that you will move or copy to the master workbook.

Notes:

1). You can select multiple non-adjacent worksheets with holding the Ctrl key and clicking the sheet tabs one by one.

2). For selecting multiple adjacent worksheets, please click on the first sheet tab, hold the Shift key, and then click the last sheet tab to select them all.

3). You can right click on any sheet tab, click on Select All Sheets from the context menu to select all worksheets in the workbook at the same time.

3. After selecting the needed worksheets, right click the sheet tab, and then click Move or Copy from the context menu. See screenshot:

4. Then the Move or Copy dialog pops up, in the To book drop-down, select the master workbook you will move or copy worksheets into. Select move to end in the Before sheet box, check the Create a copy box, and finally click the OK button.

Then you can see worksheets in two workbooks combined into one. Please repeat the above steps to move worksheets from other workbooks into the master workbook.


Combine multiple workbooks or specified sheets of workbooks to a master workbook with VBA

If there are multiple workbooks need to be merged into one, you can apply the following VBA codes to quickly achieve it. Please do as follows.

1. Put all workbooks that you want to combine into one under the same directory.

2. Launch an Excel file (this workbook will be the master workbook).

3. Press the Alt + F11 keys to open the Microsoft Visual Basic for applications window. In the Microsoft Visual Basic for applications window, click Insert > Module, then copy below VBA code into the Module window.

VBA code 1: Merge multiple Excel workbooks into one

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.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
	

Notes:

1. The above VBA code will keep the sheet names of the original workbooks after merging.

2. If you want to distinguish which worksheets in the master workbook came from where after merging, please apply the below VBA code 2.

3. If you just want to combine specified worksheets of the workbooks into a master workbook, the below VBA code 3 can help.

In VBA codes, “C:\Users\DT168\Desktop\KTE\” is the folder path. In the VBA code 3, "Sheet1,Sheet3" is the specified worksheets of the workbooks you will combine to a master workbook. You can change them based on your needs.

VBA code 2: Merge Workbooks into one (each worksheet will be named with prefix of its original file name):

Sub MergeWorkbooks()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\DT168\Desktop\KTE\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

VBA code 3: Merge specified worksheets of workbooks into a master workbook:

Sub MergeSheets2()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next

xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"

xArr = Split(xStrName, ",")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

4. Press the F5 key to run the code. Then all worksheets or specified worksheets of the workbooks in the certain folder are combined to a master workbook at once.


Easily combine multiple workbooks or specified sheets of workbooks to one workbook

Fortunately, the Combine workbook utility of Kutools for Excel makes it much easier to merge multiple workbooks into one. Let's see how to get this function work in combining multiple workbooks.

Before applying Kutools for Excel, please download and install it firstly.

1. Create a new workbook and click Kutools Plus > Combine. Then a dialog pops to remind you that all combined workbooks should be saved and the feature can't be applied to protected workbooks, please click the OK button.

2. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one workbook option, and then click the Next button. See screenshot:

3. In the Combine Worksheets - Step 2 of 3 dialog box, click the Add > File or Folder to add the Excel files you will merge into one. After adding the Excel files, click the Finish button and choose a folder to save the master workbook. See screenshot:

Now all workbooks are merged into one.

Compared with the above two methods, Kutools for Excel has the following advantages:

  • 1) All workbooks and worksheets are listed in the dialog box;
  • 2) For the worksheets you want to exclude from merging, just uncheck it;
  • 3) Blank worksheets are excluded automatically;
  • 4) The original file name will be added as prefix to the sheet name after merging;
  • For more functions of this feature, please visit here.

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


Kutools for Excel - Helps You Always Finish Work Ahead of Time, Have More Time to Enjoy Life
Do you often find yourself playing catch-up with work, lack of time to spend for yourself and family?  Kutools for Excel can help you to deal with 80% Excel puzzles and improve 80% work efficiency, give you more time to take care of family and enjoy life.
300 advanced tools for 1500 work scenarios, make your job so much easier than ever.
No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
Complicated and repeated operations can be done a one-time processing in seconds.
Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
Become an Excel expert in 3 minutes, help you quickly get recognized and a pay raise promotion.
110,000 highly effective people and 300+ world-renowned companies' choice.
Make your $39.0 worth more than $4000.0 training of others.
Full feature free trial 30-day. 60-Day Money Back Guarantee without reason.

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Shuk · 3 years ago
    I have used below mentioned script and it was successful :-)

    Sub GetSheets()
    Dim temp As String
    Path = "Z:\.....\"
    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

    However facing issue with two different format of excel spread sheets i.e. [b]"xls"[/b] and [b]"xlsx"[/b] which i would like to import. Any help would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    Ghulam · 3 years ago
    @Ginger41 Hi
    I added the code into a module. Named the Excel book Masterfile.

    Where in the code do I add. thank you
  • To post as a guest, your comment is unpublished.
    Johnny Bravo · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Kevin Coutts · 4 years ago
    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\Combine Excel Workbooks\Workbooks 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:=ThisWorkbook.Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub
  • To post as a guest, your comment is unpublished.
    Ma Hi · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Ma Hi · 4 years ago
    @jlhall07 Maybe you should change
    ReadOnly:=True
    to
    ReadOnly:=False,
    I have done and it was helpful
  • To post as a guest, your comment is unpublished.
    E nic · 4 years ago
    Several excel files to merge using access?
    There are several files on the network.
  • To post as a guest, your comment is unpublished.
    E nic · 4 years ago
    @A. Karthi 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?
  • To post as a guest, your comment is unpublished.
    Chris F · 4 years ago
    @Susie 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
  • To post as a guest, your comment is unpublished.
    Chris F · 4 years ago
    @Susie [quote name="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[/quote]
    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.
  • To post as a guest, your comment is unpublished.
    Jan Drozd · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Mohideen · 4 years ago
    @Niki Foster 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.
  • To post as a guest, your comment is unpublished.
    Mohideen · 4 years ago
    @kannan 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.[quote name="kannan"]THANKS FOR THIS VALUABLE FORMULA....[/quote]
  • To post as a guest, your comment is unpublished.
    Mohideen · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    kannan · 4 years ago
    THANKS FOR THIS VALUABLE FORMULA....
  • To post as a guest, your comment is unpublished.
    pat · 4 years ago
    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\Diabetes 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:=ThisWorkbook.Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub
  • To post as a guest, your comment is unpublished.
    BWANG · 4 years ago
    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:=Workbooks("Weather data 201611.xlsx").Sheets(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
  • To post as a guest, your comment is unpublished.
    Rakesh G.B.Groups · 4 years ago
    i want to mix or update a excel file by a old file data and new file data
    please suggest me command

    Thank you
  • To post as a guest, your comment is unpublished.
    wackiemark · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Ashwath · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    cindy · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    MUHAMMAD MURTAZA · 4 years ago
    ASSALAM O ALIKUM,,

    LOT OF THANKS FOR COMBINE WORK SHEET IN MASTER SHEET
  • To post as a guest, your comment is unpublished.
    Gnanesh · 4 years ago
    Thank you very much for the script for combining workbooks!
  • To post as a guest, your comment is unpublished.
    Hoang · 4 years ago
    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 ?
  • To post as a guest, your comment is unpublished.
    Taslima · 4 years ago
    @A. Karthi 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.
  • To post as a guest, your comment is unpublished.
    wooly · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    samuel Birch · 4 years ago
    @Susie 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:=ThisWorkbook.Sheets(1)
    'Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub




    Hope this helps!
  • To post as a guest, your comment is unpublished.
    Gaurav Sethi · 5 years ago
    Hi,

    My name is Gaurav, I have 10 Excel workbook & i want to add all file in one workbook. Please suggest.
  • To post as a guest, your comment is unpublished.
    Shubham · 5 years ago
    GREAT STUFF!! You saved me a lot of effort!
  • To post as a guest, your comment is unpublished.
    Summer · 5 years ago
    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
  • To post as a guest, your comment is unpublished.
    Zadius · 5 years ago
    @RichardHead [quote name="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\"[/quote]

    This worked for the people saying nothing happens!!! Thanks soooo much!
  • To post as a guest, your comment is unpublished.
    RichardHead · 5 years ago
    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\"
  • To post as a guest, your comment is unpublished.
    Adriano Marcato · 5 years ago
    @Code 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?
  • To post as a guest, your comment is unpublished.
    DaisyD · 5 years ago
    Thanks for this. Only your website gave me the solution for what I was looking for. Very helpful and correct.
  • To post as a guest, your comment is unpublished.
    Samuel · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    krishna sharma · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Sourabh · 5 years ago
    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...
  • To post as a guest, your comment is unpublished.
    Greg · 5 years ago
    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...
  • To post as a guest, your comment is unpublished.
    Chris · 5 years ago
    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:=CUsers \ 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
  • To post as a guest, your comment is unpublished.
    fux · 5 years ago
    @Greg It seems that "Path" is now reserved so use just any other name and replace "Path", e.g. "Mypath".
  • To post as a guest, your comment is unpublished.
    Snabo · 5 years ago
    Excellent VBA code. Thanks
  • To post as a guest, your comment is unpublished.
    Sachin · 5 years ago
    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)
  • To post as a guest, your comment is unpublished.
    imran · 5 years ago
    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 !
  • To post as a guest, your comment is unpublished.
    vijay · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    arun kumar · 5 years ago
    hi i am using ur coding its show error
  • To post as a guest, your comment is unpublished.
    Joanne K · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    nEELAM SHARMA · 6 years ago
    pls thanks for your guidelines
  • To post as a guest, your comment is unpublished.
    Karan kumar · 6 years ago
    Woooowww... this is a great software for me !!!

    My all work is done
    thanks a lot for this
  • To post as a guest, your comment is unpublished.
    swattie · 6 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Purnendu Biswas · 6 years ago
    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