--------------------------------------------------------------------------------

I have this spreadsheet that is linked to the internet. It uses some add in functions to extract info from the internet. As soon as I open this sheet, all the values get updated. Now, this sheet is huge. It takes around 5-10 minutes to extract all the info. I have another spreadsheet which has a macro that copies relevant info from the first spreadsheet and pastes special the values. And this macro is a part of a much larger program.

Now the problem: Once I run this program, the macro just opens the first spreadsheet and copies from there and pastes special. The trouble is that the first spreadsheet hasnot calculated anything by that time. So what I get is a 1000 #N.A.s. How can I ask the macro not to run till the first spreadsheet has stopped calculating?
(I have tried the wait function. But firstly, I need to give an absolute time and I dont want to change the code everytime I have to run the program (No point of whole automation). Also, the time taken by spreadsheet randomly varies between 2 to 10 minutes depending on the processor speed).

Please help.