separate tables

[Mhnd]

I have a weekly report where I need to gather data from 50 Excel files that contain around 120 sheets. Initially, I had to input these sheets individually, which was tedious. Now, I’m using batch macros to read all the Excel sheets simultaneously, followed by another macro that combines all the data into a single dataset. This process has saved me from the previous manual work. However, since almost every sheet has a different schema, I want to separate the combined data based on the filenames, which include the sheet names. My goal is to dynamically organize each table with its corresponding columns without manually filtering or setting conditions 120 times. Ultimately, I will create a workflow to clean and restructure the data for further manipulation.

[binuacs]

@Mhnd you may need one more batch macro to process the files separately. 

[Mhnd]

How is it going to be? One more thing: I want to reduce the amount of time as much as I can. If I do one more batch macro, that means I will open the workflow separately. Is that correct? 

[Mhnd]

How is it going to be? One more thing: I want to reduce the amount of time as much as I can. If I do one more batch macro, that means I will open the workflow separately. Is that correct? 

[binuacs · Accepted solution]

@Mhnd my understanding is that you want to combine a set of files which having common name? to do that you can have one more batch macro, it will not consume any amount of time