Excel: Complicated (I think) Macro

Hi I have looked through your forums before joining in hope that the answer as there but haven' found it. Basically I want to run a macro or something similar that will bring back the information from a set data range of all spreadsheets in that folder. Basically I am having a form that will be completed by other people which is in a set template format. I want to have a macro that will drag through the data range from each form within the folder automatically. For example There are 3 forms completed in the folder. I want my central data base to bring through the data on each form from cell B2. And relay it into the central spreadsheet eg. Master spreads sheet will look like this after the Macro has run A1 = 4 B1 = 2 C1 = 3 This basically has dragged through the data from cell B2 in each of the following spreadsheets which are all saved in a central folder. Spreadsheet 1 – Cell B2 = 4 Spreadsheet 2 – Cell B2 = 2 Spreadsheet 3 – Cell B2 = 3 If I was then to add another spreadsheet into the folder “spreadsheet 4” which has the value of 7 in cell B2, then I run the Macro again then this data will then be added to cell D1 accordingly of the master sheet. Spreadsheet 5 will then feed into cell E1, spreadsheet 6 will feed data in to cell F1 of the mastersheet and so on. Obviously if the data was static and not being added to with new spreadsheets then I could do a Vlockup or similar, but I’m sure there is a way to do this where it just looks for all spreadsheets within a folder. Hopefully someone can help me with this please. Thanks in advance

This question generated 12 answers. To proceed to the answers, click here.

This thread is current as of December 17, 2014.

For more resources for Microsoft Excel