Consolidate all data from multiple files into single excel file

[lilit2222]

Hi All,

I have dozens of excel files (each having number of sheets). There is exactly the same data in all files and sheets with the same headers

1. All the files are named differently
2. All the workbooks have the same number (4) of sheets with same names ("001", "004", "058", "002").

I would like to write a vba code that all data from different files will be consolidated into single new excel file without actually opening the files???

I need this as I have dozens of files and it will take a long time to manually copy paste data manually.

I will be very thankful for your help.

See attached the sample of one of those files.

[Mumps1]

Do you want to place each sheet from each source files to a sheet with the corresponding sheet name in the destination file? This would mean that the destination file also has the same four sheets. What is the full path to the folder containing the files and what is the extension of the files (xlsx, xlsm)? Are the source files the only files in that folder?

[lilit2222]

Hi and thank you for your reply.

1. The destination file will have the same number of sheets and names.
2. The full path of source files is C:\Users\emil abrahamyan\Desktop\Due Diligence Creation and extension is xlsx
3. Yes, the source files will be the only files in the folder.


P.S. Please note, that although the source files will have the same number of sheets and names, there might be sheets which do not have any data at all- therefore this should be skipped.

I hope this answers to your question.

Thank you very much.

[Mumps1]

This macro assumes that there are 4 sheets in the destination workbook with the same names as the sheets in the source workbooks. It also assumes that the four sheet in the destination workbook already include the same headers in row 1.

Please Login or Register  to view this content.

[lilit2222]

HI and thank you for your reply.
I have tried it but there is runtime error 9 and the line starting with ws.UsedRange.Offset is highlighted.

Could you advise on this please? thanks

[Mumps1]

Are the sheet names in the destination workbook the same as those in the source workbooks?