Merge Excel Files with different tab/sheet names

Question

Hi team,

I have around 1000 files in a folder. Each file has multiple worksheets. Some files have sheets with names A, B, C, D1 & E. Some other files have sheets with names A, B, C, D2 & E. Please note each file will have either D1 or D2 (not both).

Data in sheet D1 starts from Row 10. Data in sheet D2 starts from Row 5. Tab D2 has all columns same with D1 except that it has one additional column.

I want to merge all the files together taking only D1 or D2 based on which sheet that file contains.

Can you please help?

SPetrie · Answer

I created a workflow and macro combo that should do this ask if im understanding it correctly.

Let me know if this helps.

TestFlow.yxzp

SPetrie · Answer

A batch macro is going to be your best bet for this. When you say you only want to take D1 or D2, do you mean all other sheets should be ignored when bringing in data? Also, are you wanting D1 and D2 to be stacked in the final output as a singular D column, or do they still need to be D1 and D2?

Edit: Dont know why I was talking about stacking D1 and D2 since they are sheets and not columns. Ignore that part of my question. :)

allwynthomas24 · Answer

Hey @Praneeth1996, it would be good if you could provide some of the data files to work out on the solution.

Regards,

Allwyn.