Macro to collect data from multiple closed spread sheets.

**Awangkualizul** · 01-27-2013, 12:28 AM

Hi,

I'm a new member in this forum and I'm very new to macro/vba world. I would like to know how to create a macro to extract data from closed spread sheets.

For example,

I've spreadsheet on daily room temperature, weather, etc. Everyday a spread sheet with the daily data will be sent to me and I need to plot all the data so that I can analyze the collection of the data, day to day.

Please if anyone can help me with this, as of now I've a lot of spreadsheets and it's quite ridiculous for me to open all the spreadsheets.

Thank you.

**jolivanes** · 01-27-2013, 01:16 AM

Are you talking about Sheets in the same Workbook or do you mean different Workbooks (files)

**JBeaucaire** · 01-27-2013, 01:33 AM

Assuming all the spreadsheets are located in the same folder and nothing else is in there,

1) Open your main workbook from where you would want to initiate this new process
2) Turn on the macro recorder
3) Record yourself opening one of your source spreadsheets, collect the data from it and paste into your main workbook, perhaps repeat with a second file
4) Turn off the recorder.
5) Paste the resulting code here in the forum along with your sample main workbook and the two source files you processed. We'll see if we can help automate that for you.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbooks.

**Awangkualizul** · 01-27-2013, 01:34 AM

Hi Jolivanes,

The sheets are coming from different workbooks (diff excel files).

Thank you.

**jolivanes** · 01-27-2013, 01:49 AM

The way JBeaucaire described is the best way to learn
In the meantime you could try this.

Have all the workbooks in the same Folder including the Workbook that has this code in it.
No other Workbooks in this Folder.
The Range to be copied is set at A1:B15. Change to suit.

Try it with copies of all workbooks in a temporary Folder first.

Please Login or Register  to view this content.

**Awangkualizul** · 01-27-2013, 08:27 AM

Hi JBeaucaire & Jolivanes..

Thanks..I'll try to do it based on your advice..I'll you know the result later since I need sometime to 'digest' and execute..

Thanks again..