Combine multiple sheets into one

[dajmosik]

Hello,

I am having trouble creating a macro that will do the following :

I have an excel file, with multiple sheets - each of them contains multiple columns. This excel file is refreshed daily, and my collegues have to report it on a weekly/monthly basis.

The idea is, that i want the macro to work as follows:

-When the person clicks 'run', the box will appear where you have to select a folder with multiple excels insde.
-Then, i want the macro to open first excel, look for sheet called "accounts", copy it (with headers) to new excel, then close the first excel, and prooceed to the second one. In the second one, do the same (but dont copy the headers) and paste the data from the second excel below the info from the first one into the new previously created excel. And reapeat that loop for every excel that was included in the selected folder.
-Then after all files are processed, save the excel with all copied information with current date and "combined accounts" in the title

The challenge here is that these 'accounts' sheets might differ slightly between each other (some of them might have hidden columns, and not always the same ones). So i would like to unhide all the columns in the 'accounts' sheet before the copying process.Additionally, the format of the data might differ(usually its the same, but you never know), so it would be good to change the data to general before copying as well.
I am kinda newbie in VBA and these hidden columns are a huge challenge for me


looking forward to your help

thanks!!