pt.PivotCache.Refresh and Date Formatting

Question

I have many pivots on many sheets on the same data sheet and I want to ensure that the pivots are always upto date on entry to the sheet. To do that I call the following sub routine and it works great apart from dates. Whatever the date format it is changed to binary, i.e. 26/05/2023 changes to 45072. How can I prevent this from happening ?

Dim ws As Worksheet

Dim pt As PivotTable

With Workbooks(ThisWorkbook.Name)

For Each ws In Workbooks(ThisWorkbook.Name).Worksheets 

    If ws.PivotTables.Count > 0 Then 

        For Each pt In ws.PivotTables 

            On Error Resume Next 

            'pt.RefreshTable 

            pt.PivotCache.Refresh 

            On Error GoTo 0 

        Next pt 

    End If 

Next ws 

End With

Answer 1

The only other suggestion I found was to select a pivot table, click Change Data Source on the PivotTable Analyze tab of the ribbon, and select the same source again. Several people reported that this took care of the problem...

Answer 2

It has

Answer 3

Has 'Preserve cell formatting on update' been ticked in the PivotTable Options dialog for the pivot tables?