I have around 20 excel files. In each of these excel files I have 3 sheets in each file.
I need to consolidate 3 sheets data in to a new sheet by creating a new sheet in the same file.
This step needs to be repeated for all 20 excel files.
Put all of the excel files in the same folder. Use the directory tool then the Load Sheet Names macro below followed by the XLSX Opener macro. These tools will open all 20 files, extract the sheet names, then open all of the sheets and union the data together. Seperate the unioned data by file name and write back to their respective files using the output tool. To use these macros without a macro repository set up, open them on your canvas and then within your workflow right click-->Insert Tool-->Macros and you should see the macros listed to select.