Merge worksheets from multiple workbooks in a folder to 1 workbook

[jk51]

Does anyone know a VBA code in 2 steps in excel
1 Open folder path
2 Click button "Merge all sheets in 1 file" - Merge all worksheets from multiple workbooks (3 files containing various number of sheets and different name some might be 2 or 3 or 4 sheets each. Note if sheet name are same e.g. sheet1 and another file contain sheet1 and merge you see output sheet1, sheet1(2) not overwrite)

For example :

1 folder contains 3 excel workbook files.
Excel workbook file 1 contains 2 sheets ("A","B")
Excel workbook file 2 contains 3 sheets ("B","D","E")
Excel workbook file 3 contains 2 sheets ("G","J")

Output:

New Excel workbook called "Mergeallsheets" with 7 sheets ("A","B","B(2)","D","E","G","J")
Message box say "Merge all completed".

Thank you in advance for any help.

Mr Singh

 

[Monty]

Hello jk51

Welcome back!!!!

Try this attached file as per your requirment.

Check the code before you run...Need to change path as per your needs.

Hope you like it.

 

[Arpanakumar]

Hello Monty.

I have similar kind of question but with subfolder's..is it possible?

 

[jk51]

Hi Monty,

Thanks for this. Mine is not working.

I edit the path name and save the file. I open the file (jk51_Copy Sheets.xlsb) and it was blank file. Not sure why you are using the extension .xlsb not .xlsm. I ran the macro test but no output. I am using Excel 2010.

Not sure this line: Windows("test.xlsm").Activate 'Change file name.
Give me an example.

I forgot to add 3 more steps after the mergesheets:
step 1
Is it possible to create a new sheet called "Appended" to append all the multiple sheets all in 1 after the mergesheets. Note all the sheets contain the first row headers are all the same fields.

step 2
Is it possible to create a new sheet called "Summary" contains a table of all the multiple sheets name in a file and their number of row counts

step 3
Is it possible to create a new sheet called "Remove duplicates" to highlight the append table and remove the duplicate rows.

Thank you very much.

Bw

Mr Singh

 

[jk51]

Hi Monty,

I tried my VBA code to get multiple sheets and manually edit the path.

Is there a way of editing the code I want to specify the folder path Click the folder path

Option Explicit
Sub MergeMultiSheets()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\TESTFOLDER\" 'Change as needed
FileName = Dir(Path & "\*.xlsx", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Application.DisplayAlerts = False
Sheet1.Delete
Application.DisplayAlerts = True

End Sub

Hello jk51

Welcome back!!!!

Try this attached file as per your requirment.

Check the code before you run...Need to change path as per your needs.

Hope you like it.

 

[jk51]

Hi Monty,
Thanks for this. Mine is not working.
I edit the path name and save the file. I open the file (jk51_Copy Sheets.xlsb) and it was blank file. Not sure why you are using the extension .xlsb not .xlsm. I ran the macro test but no output. I am using Excel 2010.
Not sure this line: Windows("test.xlsm").Activate 'Change file name.
Give me an example.
I forgot to add 3 more steps after the mergesheets:
step 1
Is it possible to create a new sheet called "Appended" to append all the multiple sheets all in 1 after the mergesheets. Note all the sheets contain the first row headers are all the same fields.
step 2
Is it possible to create a new sheet called "Summary" contains a table of all the multiple sheets name in a file and their number of row counts
step 3
Is it possible to create a new sheet called "Remove duplicates" to highlight the append table and remove the duplicate rows.
Thank you very much.
Bw
Mr Singh

Hello jk51

Welcome back!!!!

Try this attached file as per your requirment.

Check the code before you run...Need to change path as per your needs.

Hope you like it.

 

[Monty]

Hello Sing.

Your 3 points are covered in attached file...Please check and let me know any challenges.

1) Appends data.
2) Summary Table
3) Remove duplicates in Append sheet

Hope you like this!!!1

 

[jk51]

Hi Monty,

You are a star. You should deserve more than 3 points for this!

Two more to amend the summary table, how to add 2 new columns in the summary table,
i) count number of unique rows in multiple sheet names and ii)count number of duplicates in multiple sheets names in a excel workbook file and at the bottom of the table to display total number of rows and unique rows and duplicate rows.

Secondly, Is there another button to reset all the macro to start again.

That will be fantastic if this all works.

Thank you very much.

Mr Singh

 

[Monty]

Hello Singh.

Please find attached.

Your two questions with solutions.

1) Getting duplicates in column "C" (Summary table)
2) Reset summary table data.

 

[jk51]

Thanks Monty. Much appreciated.

Best wishes

Mr Singh