Pivot Table Data 'Refresh data when opening file'

[clh_1496]

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 that converts all the fields in a selected pivot table to the count function or other functions with VBA Macro in Excel and wondered if this could be changed to tick the 'Refresh Data when opening the file' box instead?

The reason I'm looking for this is that I create a daily report and every day the data source has to be changed and I currently use a VBA to do this for the entire worksheet. I then manually ticked 'refresh data when opening the file' in every pivot which worked great, but then as soon as the changed the data source the next day, the boxes unchecked?

Public Sub PivotFieldsToCount()
    Dim pf As PivotField
    With Selection.PivotTable
        .ManualUpdate = True
        For Each pf In .DataFields
            With pf
                .Function = xlCount
                .NumberFormat = "#,##0"
            End With
        Next pf
         .ManualUpdate = False
     End With
 End Sub

Kind regards

Charlotte

[#2 · Accepted solution]

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

@clh_1496 

[#3]

Amazing, thank you!