I have a macro code that runs a MSQuery returning a table of data to a specified worksheet. I have a pivot table (on a separate worksheet) that uses that table as source data. The same macro that runs the MSQuery also refreshes the pivot table. For some reason the macro must be run twice to get the pivot table to refresh.
The table of data is imported just fine on the first run. Not sure what the issue is. I have tried multiple variations of code to refresh the pivot tables. I even put the code in twice.
Thanks for any help.
I have two macros that I use often, one to refresh pivot tables, another to refresh queries, you could try to call them at the end of your code
VB:Public Sub RefreshQueryTables() Dim w As Worksheet Dim q As QueryTable On Error Resume Next For Each w In ActiveWorkbook.Worksheets Application.StatusBar = "Refreshing Query Table: " & w.Name For Each q In w.QueryTables q.Refresh Next Next End Sub Public Sub RefreshPivotTables() Dim w As Worksheet Dim p As PivotTable Dim pf As PivotField Dim pi As PivotItem On Error Resume Next For Each w In ActiveWorkbook.Worksheets Application.StatusBar = "Refreshing Pivot Table: " & w.Name For Each p In w.PivotTables For Each pf In p.PivotFields For Each pi In pf.PivotItems pi.Delete Next Next p.RefreshTable p.Update Next Next End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)