Long Macro Running too Fast actually Pastes "#GETTING_DATA"

I've got a weird problem. I'm running ActiveWorkbook.RefreshAll to get data from SQL via PowerQuery, and then I have a long macro to do some refreshing within the workbook.

When I run them separately, everything is great. But I run into trouble with I try to run one after the other.

Specifically, there's one data set that I refresh and then copy paste special, but the macro is running slow and so it's actually pasting "#GETTING_DATA"

I've tried Application.Wait in a few different places. I've also tried enabling screen updating. And nothing works.

Someone suggested disabling background refresh in the connections properties. Mine is grayed out, but it seems to already be disabled.

So...any ideas?

And I hope that makes sense.

Hi xladept, thanks for the reply. I usually try to attach sample workbooks, but there's just no way with this one because of the PowerQuery connection.

Someone suggested I try DoEvents, but that didn't change anything. I also tried to do the refresh on Workbook_Open, and using a message box after ActiveWorkbook.RefreshAll to trigger the other macros, and still no change.

I'm about to admit defeat. Any other ideas?

Ok...this might not make a lot of sense without context, but here it is:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks for your help!! I hope that makes sense.

Norie, I got rid of all cutting and pasting until the final step. The code is cleaner, but it still doesn't work!

BTW, the Refresh macro works perfectly on its own. It just has trouble with the last step when combined with RefreshAll.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

xladept, I'm not sure what that is. Where would it go?

I have a working solution. Finally!

Please Login or Register  to view this content.

It delays the data refresh and then my second macro runs as it normally does.

Thanks so much for your help, everyone!

Hello phelbin,

Can't thank you enough for introducing me to

Please Login or Register  to view this content.

method. I would just like to add definition for this method from Microsoft's VBA reference so others be more confident:

Runs all pending queries to OLEDB and OLAP data sources.