Refresh pivot tables but not external data source

Question

I have a spreadsheet with multiple tables, where the data is pulled from an external data source (SQL database). The connections/tables refresh by changing an option in a drop down box and then pressing a button to run a VBA.

Attached to each of these tables is a pivot table. The pivot tables don't refresh with the tables. If I try pressing refresh all I get the error;

'Data Source name not found and no default driver specified'

However if I go through the spreadsheet and hit refresh on each individual pivot table they update without the error.

So either I need some way to get the pivot tables to refresh with the tables or have a button that refreshes only the pivot tables and not the external data connections.

Any ideas appreciated, I don't know where to begin with this one!

Answer 1

You can refresh a given PivotTable on Sheet1 like this:

Sheet1.PivotTables(1).RefreshTable

That will refresh the first PivotTable on Sheet1. Change the index number for a different one.

Or...

You can refresh all of the PivotTables on a give sheet by calling this routine:

Sub RefreshPivotTables(ws As Worksheet)
    Dim pt As PivotTable
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next
End Sub

You would call the above routine from the same code associated with the button mentioned in your question that updates the tables.

Or...

If you'd like to update all of the PivotTables in a workbook, you can use this version of the routine:

Sub RefreshPivotTables(wb As Workbook)
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next
    Next
End Sub

You would call this version like so:

RefreshPivotTables ThisWorkbook