VBA to Refresh Pivot Table

[Candyman8019]

I have been trying to automate some refreshing of pivot tables when a tab is activated. I have it working just fine on my system with O365; however, my wife's employer uses Office Pro 2016 and it doesn't work for her. Here's the code that is returning an error 1004 for her:

Private Sub worksheet_activate()
Dim pc As PivotCache

'Refresh Pivot Caches
For Each pc In ActiveWorkbook.PivotCaches
    pc.Refresh
Next pc

'Refresh Table
Sheet1.PivotTables("PivotTable2").RefreshTable

end sub

Any thoughts on what I would need to do differently for this to work on Excel 2016?

Thanks.

Joe

 

[dragontbone]

Hey, so there are a few issues that might be causing this.
Worksheet Reference: Make sure that the worksheet you're trying to activate and the pivot table you want to refresh are in the same workbook. If the workbook is opened in compatibility mode in Office 2016, you might encounter issues.

Worksheet Name: Verify that the worksheet you're trying to work with is indeed named "Sheet1" in your Excel file. If it has a different name, you should use that name in your code.

PivotTable Name: Ensure that the pivot table you want to refresh is named "PivotTable2" on the specified worksheet. If it has a different name or is on a different sheet, you should adjust the code accordingly.

Here is a code that has error handling that may help pinpoint where the issue is occurring at.

Private Sub Worksheet_Activate()
    Dim pc As PivotCache
    Dim ws As Worksheet
    Dim pt As PivotTable

    On Error Resume Next ' Ignore errors temporarily

    ' Set references to the worksheet and pivot table
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set pt = ws.PivotTables("PivotTable2")

    On Error GoTo 0 ' Reset error handling

    ' Check if the worksheet and pivot table were found
    If ws Is Nothing Then
        MsgBox "Worksheet 'Sheet1' not found.", vbExclamation
        Exit Sub
    End If

    If pt Is Nothing Then
        MsgBox "Pivot table 'PivotTable2' not found on 'Sheet1'.", vbExclamation
        Exit Sub
    End If

    ' Refresh Pivot Caches
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc

    ' Refresh Pivot Table
    pt.RefreshTable
End Sub

 

[Candyman8019]

Hey, so there are a few issues that might be causing this.
Worksheet Reference: Make sure that the worksheet you're trying to activate and the pivot table you want to refresh are in the same workbook. If the workbook is opened in compatibility mode in Office 2016, you might encounter issues.

Worksheet Name: Verify that the worksheet you're trying to work with is indeed named "Sheet1" in your Excel file. If it has a different name, you should use that name in your code.

PivotTable Name: Ensure that the pivot table you want to refresh is named "PivotTable2" on the specified worksheet. If it has a different name or is on a different sheet, you should adjust the code accordingly.

Here is a code that has error handling that may help pinpoint where the issue is occurring at.

Private Sub Worksheet_Activate()
    Dim pc As PivotCache
    Dim ws As Worksheet
    Dim pt As PivotTable

    On Error Resume Next ' Ignore errors temporarily

    ' Set references to the worksheet and pivot table
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set pt = ws.PivotTables("PivotTable2")

    On Error GoTo 0 ' Reset error handling

    ' Check if the worksheet and pivot table were found
    If ws Is Nothing Then
        MsgBox "Worksheet 'Sheet1' not found.", vbExclamation
        Exit Sub
    End If

    If pt Is Nothing Then
        MsgBox "Pivot table 'PivotTable2' not found on 'Sheet1'.", vbExclamation
        Exit Sub
    End If

    ' Refresh Pivot Caches
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc

    ' Refresh Pivot Table
    pt.RefreshTable
End Sub

As I step through the code it errors out on pc.Refresh. Run-time error '1004': Application-defined or object defined error. This errors out on Excel 2016, but works fine on O365.

 

[dragontbone]

As I step through the code it errors out on pc.Refresh. Run-time error '1004': Application-defined or object defined error. This errors out on Excel 2016, but works fine on O365.

It may be you need to refresh each pivot table individually. Try this code:

Private Sub Worksheet_Activate()
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim pc As PivotCache

    ' Set the worksheet reference
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Check if the worksheet was found
    If ws Is Nothing Then
        MsgBox "Worksheet 'Sheet1' not found.", vbExclamation
        Exit Sub
    End If

    ' Loop through and refresh pivot tables
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt

    ' Loop through and refresh pivot caches
    For Each pc In ThisWorkbook.PivotCaches
        pc.MissingItemsLimit = xlMissingItemsNone ' Optional line to handle missing items
        pc.Refresh
    Next pc
End Sub

This should loop through each pivot table on the specified worksheet and refresh them individually. Then, it will loop through the PivotCaches in the workbook and refresh them. If this still results in the same error on Excel 2016, it might be due to a specific issue with the version of Office 2016 your wife's employer is using. In that case, it may be necessary to check for any available updates or patches for Office 2016 that could address this issue.

 

[Candyman8019]

Still no luck. Thanks for trying...

 

[dragontbone]

Still no luck. Thanks for trying...

Sorry man, good luck.