VBA: Refresh Issue

RRS

New Member
Joined
Mar 29, 2022
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
Hello, All.

I need some assistance. I have a macro that modifies my data query. Once the query is modified I need to refresh all my tables, query and all my pivot tables. I created a macro to refresh my data but I have to run the macro twice so the pivot tables can make the updated corrections.

How can I correct this code?
OR
How can I have the refresh macro run twice?


Any help would be greatly appreciated. Please see VBA code below:



Sub Refresh_Data()

'Refresh data source
ActiveWorkbook.RefreshAll

'Refresh Pivot Tables
Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables
PT.RefreshTable
Next PT

Next WS

'Refresh Worksheet in "EmpLoan PTable" - Worksheet is hidden
Worksheets("EmpLoan PTable").PivotTables("PivotTable1").RefreshTable


'Message Box
MsgBox "All Data Tables Refreshed"


End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
to refresh twice, create a macro that you run. This macro calls Refresh_Data twice.
VBA Code:
sub DoRefresh()
    Refresh_Data
    Refresh_Data
end Sub
 
Upvote 0
Having to refresh twice tends to be an issue if you use Power Query to populate a table and then have a the pivot stacked on top of that table.

Per Celia Alves, this can be fixed by:- on the query properties uncheck "enable background refresh"
 
Upvote 0
Yes it will be fixed that way. Then you could also use:

VBA Code:
Thisworkbook.refreshall
 
Upvote 0
Having to refresh twice tends to be an issue if you use Power Query to populate a table and then have a the pivot stacked on top of that table.

Per Celia Alves, this can be fixed by:- on the query properties uncheck "enable background refresh"
This worked perfectly. Thank you.

Unbelievable how easy that was.

Thank you Again
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top