need help to complied Secound sheet of all excel file in a folder

[nkashyap3]

Hi Friends,

I need help to resolve my query, I have 50 to 100 excel files which I need to compile all files in one new excel sheet. there some point where I am stuck.
1- only complied the data of Sheet2 (tab name Details)
2 Main Data start will start row 3 (Row 2 is header)
3 in new sheet create the Header only first time after that copy paste data of all excel files one by one
4- Folder Location is not fixed where all excel files are available.

Header is (Name, Emp ID, Employee Type, Country, City, LOB)


I have attached the 3 sample files for format.

[Mumps1]

When you say "Folder Location is not fixed where all excel files are available.", do you mean that the files are all in one folder but the folder location varies or do you mean that the files can be in different folders? Also, do you want to copy only theses columns: Name, Emp ID, Employee Type, Country, City, LOB

[nkashyap3]

All files available in one folder but the folder location is not fixed, every month will change the folder location

[nkashyap3]

All Header according to attached file all files header row is same ,

[Mumps1]

Place this macro in a new, blank workbook. Rename the first sheet as "Summary". Run the macro. You will be prompted to select the folder containing your files.

Please Login or Register  to view this content.

[nkashyap3]

Hi There Some Issue in Summary table showing multipal header row , for example if we have copy the data form 3 files , in summary tab showing 3 header row ,

and range are copy from column 1 , some data are not copy if we get any blank cell in column 1,

example in one file we is 100 row data except header and column A has first 75 cells in first column, we getting only 75 rows data in summary tab , according to the logic all 100row data should be in summary

[Mumps1]

Try:

Please Login or Register  to view this content.