Reading and compiling multiple sheets then tranpose

[PTM98]

Hi guys! 

Trying to read multiple sheets from a workbook but I'm getting stuck at the dynamic tool, for some reason it does not take all the data. I am attaching the file, as well as the desired outcome.

I used this config on the input tool 

And this one for the dynamic tool

I get the warnings that they are not the same as the template, although it is the same, except for a couple of sheets, which are shorter in terms of the period analysed.

Therefore I just want to put each of them under this format, but I think I will manage to do it with transpose (?) so on and so forth for each country in the project.

Any suggestion would be much appreciated!! Thanks a lot in advance guys!

[Qiu]

@PTM98 
There is an existing macro we can bring the data with different schema.
https://community.alteryx.com/t5/Community-Gallery/Dynamic-Multi-File-Sheet-Names/ta-p/919218

[FrederikE · Accepted solution]

Hey @PTM98,

You can use a batch macro instead.

Important:

- Exchange the sheet name with the inputs into the macro (You need to redo this after changing the path to yours) (Fig.1)

- Use the Property setting that they columns are stacked on top by position/name (Fig. 2)

[Qiu]

@PTM98 
We can do a Batch Macro as below, note that some data cleansing required/.

[PTM98]

Thanks everyone for their time!! Used it, but did not have the time to post again, forgot about. Thank you again!