This is a Excel VBA macro project to create a macro that pulls information from multiple sheets into one report.
The macro needs to gather data from a number of different uniform spreadsheets (same template) into one complete sheet. Each individual Excel spreadsheet is for a different project and all relevant data in each template will be stored on row 2 on a tab called “ExportData”. The location of the files will change (i.e. different people will be running the Report Macro) but the project spreadsheets will always be in the same folder as the Report Macro Excel file.
I need a macro that will:
-Be stored in an Excel file called Report.
-It needs to copy line 2 on “ExportData” tab from every Excel file that is in the same folder as the Report Excel file. Note that line 2 column count is not yet known so it should pull the whole row.
-Needs to pull all of these rows into the Report Excel beginning on line 2 (line 1 will be the description of what each column is).
-Needs to confirm it is finished and how many files it pulled.
-Needs to provide pop up warning if an Excel file does not have “ExportData” tab or similar pull error, but be able to continue once you acknowledge the error.
-Needs to first clear all of the data on the Report sheet except for the row 1 descriptions (i.e. resets every time it is run).
-The Report sheet needs two buttons – One being “Run Report” to run the macro, and a second button called “Clear” to remove all report data.
-Needs to have notes describing what parts of the code do.
-Needs to be unlocked, not password protected.
Note – If you can also make a second version of the macro that only pulls information from new files added since the macro was last run - instead of deleting and re-adding everything from scratch - this would be very helpful. There are cases where a few additional reports might be added after an initial run. If you can do this please note in your response.