Quote:
Originally Posted by psrs0810
I need help with building a macro to pull data from over a 100 worksheets.
I have a questionnaire that I send out and now I need to pull the data from the worksheets. Every work sheet is the same.
To give you an example to what I need to pull, I have a question: Is your facility a standalone or a system?
In cell C10 and E10 there would be a box for them to put an X. I need to know how many X's or anything typed in that cell from all of the worksheets.
To help, I have all of the worksheets saved in one folder.
Thank you for your help in advance
|
writing a macro to do this is pretty easy assuming that you really know what data is to be read from each target worksheet. All we need to do is sequence through the target worksheets, open them, read the relevant data, save it in the master, etc. If you have all the target worksheets saved in one folder than the process is even easier because we do not need a list of worksheet names; we can just sequence through every worksheet in the target folder.
The macro would work with 1 target worksheets or 5 or 500 and can read any number of things from each worksheet as long as what is read does not change from worksheet to worksheet. I assume that you would want the macro to build a table in the master worksheet indicating each worksheet name and the relevant values read. The process steps would be:
- you open the "master" and click on a button or menu pick to start the process
- a window pops up asking you to navigate to the folder containing the target worksheets
- the macro creates a new sheet in the master with a unique name for this run (probably the current date)
- the macro opens each worksheet in the folder, reads the relevant data, creates a row entry in the new sheet in the master with the target name and relevant values and then closes the worksheet
If this is essentially corrent, I can do this in 15 minutes. Let me know.