Waiting command to refresh pivot table

Hi

I have a sheet named "PivotTable" with data and its pivot table in it. I have date on another sheet and link it to "PivotTable" sheet. I use the following code to refresh the pivot table whenever I change date in another sheet. The code is embedded in "PivotTable" sheet and it works:

Please Login or Register  to view this content.

But then I want to wait for three seconds before refreshing the pivot table using the Waiting command like this:

Please Login or Register  to view this content.

Excel will keeping calculating/waiting forever until I press Esc, and then the debug will pop up and highlight the last line in yellow:

Please Login or Register  to view this content.

Any idea why this is the case, did I misuse the waiting command?


Thank you.

Hi,

Look at http://msdn.microsoft.com/en-us/libr.../ff822851.aspx and see better code that claims to use:

Please Login or Register  to view this content.

I think you need to move the errant line to a Module instead of having it behind a worksheet. It is a matter of scope.

Look at http://www.ozgrid.com/VBA/variable-scope-lifetime.htm or
http://www.cpearson.com/Excel/Scope.aspx