So I have a macro to extract a lot of data from an online database that unfortunately only allowed me to do so in a complete useless format (like this: http://imgur.com/v6bZQc4). Once you extract to excel, with the option they provide, it looks like this (for the sake of this example I deleted most rows): http://imgur.com/7Hec8dn

My macro flattens out the data to more useable format. However, I would now like to have the macro run over a folder that contains all the downloaded worksheets (the ones in the unusable format) and then generate one master sheet (in the desired format specified) from all of the files in this folder.

Some issues:

1) The second part of my macro, which adds columns for: "Country" "Month" "Year" "Transaction" are linked to a sheet1. I believe this would bring up errors and there might be a better.

2) In the master sheet generated headers must remain the same in a way that the data generated by running the macro on each downloaded sheet goes underneath.

I've tried fixing these issues and getting the macro to apply to all files in a specific folder, so far no luck. I was told to use Scripting.Filesystemobject, but I it didn't work for me - or more likely, I didn't use it properly.

Thanks in advance for any help. If this makes it easier, here's one of the downloaded worksheets and the macro in question: http://www.filedropper.com/example_2