Forum Discussion

Brass Contributor

Mar 03, 2022

Solved

Pivot Table Data 'Refresh data when opening file'

Hi, I'm looking for a VBA that runs as a macro once and ticks the 'Refresh Data when opening the file' for every pivot table in the workbook rather than having to do it manually. I saw this code...

excel

Macros and VBA

HansVogelaar

Mar 03, 2022

Sub RefreshOnOpenAll()
    Dim pvc As PivotCache
    For Each pvc In ActiveWorkbook.PivotCaches
        pvc.RefreshOnFileOpen = True
    Next pvc
End Sub

clh_1496

HansVogelaar

MVP

Mar 03, 2022

Sub RefreshOnOpenAll()
    Dim pvc As PivotCache
    For Each pvc In ActiveWorkbook.PivotCaches
        pvc.RefreshOnFileOpen = True
    Next pvc
End Sub

clh_1496

Brass Contributor

Mar 14, 2022

Amazing, thank you!