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?
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?