Hello Everyone,
I am facing an issue where the VBA script is not refreshing any pivot tables where I selected " add this data to the data model " when creating because I needed to use the Distinct Count Function. My question is if anybody know how I can use a Distinct count function without having to select "add this data to the data model" when creating the pivot table, or a VBA script that would not give me any errors when refreshing the pivot tables. This is my Script:
Sub RefreshAllPivotTables()
Dim PT As PivotTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.RefreshTable it usually gives me error '1004' here
Next PT
Next WS
End Sub
Please note that when I delete all pivot tables that use a distinct count function it works.
Any help is much appreciated.
