vba to refresh pivot table not working

[vbanovice123]

Sub RefreshPivotTables()
'
' Macro1 Macro
'
'
Sheets("Agency_Data").Select
Range("B25").Select
ActiveSheet.PivotTables("PivotTable_Agency_Data").PivotCache.Refresh
ActiveWorkbook.Close SaveChanges:=True

End Sub

 

[Misca]

Try something like:

Sub RefreshPivotTables()
Dim PT As PivotTable
    
        For Each PT In ActiveSheet.PivotTables
            PT.RefreshTable
        Next PT

End Sub

If you want to you can replace the "ActiveSheet" with "Sheets("Agency_Data")". If you do you don't have to have the Agency_Data activated to refresh the pivot tables. On the other hand if you keep it as it is, you can use the same code on every active sheet.

 

[vbanovice123]

That worked perfect for one sheet. If I dont want to navigate to the active sheet and I want to refresh multiple pivot table in multiple sheets,

Can I try something like this, separating the sheets by comma? It did not work.

Sub RefreshPivotTables()
Dim PT As PivotTable

For Each PT In Sheets("Agency_Data", "Refi_Plus_Data").PivotTables
PT.RefreshTable
Next PT
End Sub

 

[vbanovice123]

I tried this and it is working but is there a better way/

For Each PT In Sheets("Agency_Data").PivotTables
PT.RefreshTable
Next PT

For Each PT In Sheets("Refi_Plus_Data").PivotTables
PT.RefreshTable
Next PT

 

[Misca]

Try:

Sub RefreshPivotTablesOnAllSheets()

Dim WS As Worksheet
Dim PT As PivotTable
    
    For Each WS In Worksheets   'Goes through each WS
            For Each PT In WS.PivotTables  'Goes through each PT in WS
                PT.RefreshTable
            Next PT
    Next WS

End Sub

 

[vbanovice123]

Thanks a lot, this worked.

So the trick here was to loop through each worksheet and within each each worksheet to loop through each pivot table.

 

[jaysin60]

Hi Misca, will this code work when I put it in excel and refresh all data (to refresh table coming from MS SQL) ?

I have inserted a module for this, but when I refresh the whole workbook it doesn't work. Am I doing something wrong - this function should run automatically, when I refresh whole workbook, right ?

 

[Misca]

I have very little experience with SQL so I don't know for sure. It should though.

Also, the code above loops through sheets and updates the pivot tables one by one. RefreshAll should do the same and does that without loops or anything. Try:

ActiveWorkbook.RefreshAll

 

[jaysin60]

I have very little experience with SQL so I don't know for sure. It should though.

Also, the code above loops through sheets and updates the pivot tables one by one. RefreshAll should do the same and does that without loops or anything. Try:

ActiveWorkbook.RefreshAll

Thanks for your swift reply. I'll try that. However, I have a dumb question making me think; Do I have to run this code every time or will it be automatically called when I refresh those SQL-tables ?

 

[Misca]

I believe you'll have to run this whenever you've refreshed the SQL tables and want to update the new data to your pivot tables as well. You might want to add that line to one of the events in your workbook (workbook open should work) and your pivottables should be up to date whenever you open the workbook & need the data.