Pivot Table Data 'Refresh data when opening file'

Question

Hi,&nbsp;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.&nbsp; I saw this code that&nbsp;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&nbsp;the file' box instead?&nbsp;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&nbsp;the file' in every pivot which&nbsp;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 SubKind regards&nbsp;Charlotte&nbsp;

hansvogelaar · Accepted Answer

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

Forum Discussion

Pivot Table Data 'Refresh data when opening file'

2 Replies

Resources