How to merge excel files into 1 file and to name sheet according to file name?

[navnav21]

Hi I have 100 excel files and i want to merge all these files into 1 excel file with all 100 sheets. I also want to rename the sheet the same as the file name. in some excel files, they have 2 sheets. For example, Feb 2021 has 2 sheets and i want to rename them "Feb 2021 (1)" and "Feb 2021 (2)".

Can someone help create this VBA code please?

Thank you in advance!

 

[Radoslaw Poprawski]

Would not be easier to simply append all 100 of them with sheet name in a column??

this is very easy do able in powerquery.

here are some sample files (Book 3,4,5)
and my test query,

Book3.xlsx

Microsoft Excel Workbook

1drv.ms

Book4.xlsx

Microsoft Excel Workbook

1drv.ms

Book5.xlsx

Microsoft Excel Workbook

1drv.ms

test file:

Test (3).xlsx

Microsoft Excel Workbook

1drv.ms

 

[navnav21]

Would not be easier to simply append all 100 of them with sheet name in a column??

this is very easy do able in powerquery.

here are some sample files (Book 3,4,5)
and my test query,

Book3.xlsx

Microsoft Excel Workbook

1drv.ms

Book4.xlsx

Microsoft Excel Workbook

1drv.ms

Book5.xlsx

Microsoft Excel Workbook

1drv.ms

test file:

Test (3).xlsx

Microsoft Excel Workbook

1drv.ms

Hi Thanks for this, but the output is all into 1 sheet. I need them still in different sheets.

For example, i have 10 different excel files, each have 1 sheet each. i need to all these 10 sheets into 1 excel file. and for each sheet i need to rename them according to their respective file name

Currently I am running 2 VBA codes:

This merges the all the sheets into 1 excel file.

The other VBA code is to rename the sheets.

Thank you!

 

[Radoslaw Poprawski]

Thats what ull get in the last column - file name from which the data came from.

so if u merge 3 files (excelworkbooks) named A, B, C
ull get last column A, B, C respectivly

And now you can use ExcelMacroMastery advanced filter VBA solution to split this by criteria.