Consolidate some data from other excel files to a master list

**cyk6026** · 03-15-2007, 02:36 AM

Hello,

I want to create a master list to consolidate some data from other excel files automatically. I'm not sure whether I should use "Import External Data" feature, but I don't the way to function. May I have anyone's assistance? I'd like to enclose a sample file for reference.

Thanks for your assistance in advance.

**davesexcel** · 03-15-2007, 06:05 AM

Hi,
open both workbooks
in stock list 1 place this code in a module(alt F11, insert,Module)

Please Login or Register  to view this content.

run this macro when in stocklist 1

**starguy** · 03-15-2007, 06:14 AM

Originally Posted by cyk6026

Hello,

I want to create a master list to consolidate some data from other excel files automatically. I'm not sure whether I should use "Import External Data" feature, but I don't the way to function. May I have anyone's assistance? I'd like to enclose a sample file for reference.

Thanks for your assistance in advance.

the link below may help you to figure out the solution.
download the file from link and see it.
remember, that file has coloured sheets which are the same as non-coloured. purpose of coloured sheets is to use 3D reference style to get the results. in non-coloured sheets SUMIF function is used.

http://www.excelforum.com/showthread.php?t=591341

**cyk6026** · 03-15-2007, 11:29 AM

Hello,

Thanks so much for starguy's and davesexcel's response.

After I have created the macro, I found if I play it in many times, the data will be input to the master list in many times. This means that the data will be duplicated.

My situation is that I have many excel files, like "Stock List 1" and "Stock List 2". I want to create a master list to record the data from those stock lists periodically, and then generate a privot table for further process. Since the stock lists are maintained daily and the quantities are changed as well, so if possible, I want to update the master list to recognize the update information of all stock lists that I will not spend much time to look at a lot of stock lists to check the stock level.

Should you have any solutions, I apprecaite if you can suggest to me.

Thanks again.

**davesexcel** · 03-15-2007, 06:53 PM

You could clear the range with the macro before it copies and pastes, basically to reset the sheet

something like
range("A2:G400").clearcontents

here are some places to get information on how to loop through workbooks

here are some sites for looping through workbooks

http://www.ozgrid.com/News/aug-2005.htm

http://www.ozgrid.com/VBA/loop-through.htm

http://support.microsoft.com/kb/213621