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.
