This one's a good challenge - slow down a macro!

[richardff]

Oh where do I start with this!

I have a macro whose sole purpose is to open another workbook, select and copy a row of data and paste it into one central workbook. That's all it does - in all, the macro does this routine for about 150 separate workbooks (and puts the rows of data all into the one central workbook).

My problem is this: Each of those 150 workbooks has links to another application and when each workbook is opened it takes a second or two for the links to update.

Unfortunatley, my Macro is too quick and copies the row of data before it has had a chance to update (so it just copies a row of #N/A's and no data!!!)

Ideally, I need my macro to open the workbook and then pause for say 3 seconds (so the workbook can have a chance to update its links) and THEN copy the data and carry on with the rest of the routine?

Is there some sort of timer function or a pause and resume command for a macro?

Anyone have any advice or suggestions to get round this problem?

 

[Mark O'Brien]

Have a look at VBA help for "Wait", the example is pretty much what you're looking for. (I don't have Excel to bang something out for you today, sorry)

150 times 3 seconds is 450 secs, that's a long time.

 

[Dave Hawley]

Hi Richard

The OnTime Method should help, try

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "CopyIt"
End Sub

Where "CopyIt" is your macro.

 

[richardff]

Thanks Mark - I was searching in VBA help for "pause" "delay" "suspend" and everything else under the sun - except wait!!!

Cheers!