Running Data Refresh Macro, then Table Update Macro, Back-to-Back?

[chasfh]

I have two separate macros in my book: one that refreshes 12 tabs containing CSV data that points to a source outside the book (and which itself receives updated numbers each day); and one that updates data tables in other tabs in my book that rely on these 12 CSV-data tabs to be refreshed.

The idea is to refresh the CSV tabs with the outside numbers first, then update the data tables based on the newly-refreshed CSV data.

The problem I am running into is that when I try to combine the two macros, they basically run at the same time. What I typically see when I watch the book while the macro runs is that a couple of the tabs appear to refresh; then all the tables update based on the CSV data; then, when that's done, the rest of the CSV tabs appear to get refreshed.

Consequently, when this process completes, the book contains some tables that use new numbers because they happen to point to the CSV tabs that got refreshed first, and other tables using old numbers that got updated before the remaining CSV tabs got refreshed. I am trying to combine these macros so that it refreshes all 12 CSV tabs first, before it updates any data tables on other tabs. Am I making sense?

At first I tried going into VB and adding the Refresh_Tabs macro to the Update_Tables macro, with Refresh_tabs at the top, and that didn't work.

Then I found this thread:

How-To Run Multiple Macro's One After The Other

I have 4 macro's. The first macro format's data. The second macro saves the .xlsx with a unique name. The third macro merges data from other excel files. And the fourth macro saves the merged data as another .xlsx. Is there any way to run all the macro's at once and in order? If I group...

www.mrexcel.com

Which looked like it would solve my problem, but even when I followed its instructions as shown here:

Sub Update_Both()​

'​

' Update Macro​

'​

​

'​

​

Call Refresh_Tabs​

Call Update_Tables​

End Sub​

It still basically runs them at the same time, refreshing some CSV tabs, updating all the tables, and refreshing the remaining CSV tabs.

Any ideas how I can accomplish what I'm trying to here?

 

[chasfh]

Although you say you aren't using PQ the steps you took to import the data indicates you are.
If you go Data > Queries & Connections, what do you see in the pane that appears on the right hand side ?
If you do see Queries and right click and look at the properties do you see a box ticked that says Enable Background Refresh ?
You need to untick that box.

This was it. This was the whole thing right there.

I ran the ubermacro before unticking those boxes and noted whether the tables were updating with the newly-refreshed data. They were not. They still had the old data within. But then I closed the workbook without saving, re-opened it, and first thing I did was to go in and untick those boxes. I then re-ran the ubermacro, and ... perfect.

So, to all appearances, the two macros within the ubermacro were not running back-to-back, but simultaneously—or, at least, switching back and forth between the two while executing both alongside one another. That's why the tables were not updating with refreshed data, because they were updating via the second macro before the CSV tabs had finished refreshing via the first macro.

My guess is that, with Enable Background Refresh (EBR) ticked on, which allow the user to continue to edit the spreadsheet even while it is refreshing, my macro, acting as the user, was editing the tables at the same time it was also refreshing the CSV tabs, and thus, getting ahead of the refreshing and updating with the old information—which is to say, not really updating the tables at all.

But now, with those EBR boxes ticked off, they are truly executing back to back, and it's working perfectly.

This is the exact solution I needed. Thank you.

 

[Alex Blakenburg]

Thanks for letting us know. Glad that between @rlv01 and I, we were able to resolve the issue.