VBA Script not Refreshing Pivot tables

%3CLINGO-SUB%20id%3D%22lingo-sub-3111515%22%20slang%3D%22en-US%22%3EVBA%20Script%20not%20Refreshing%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3111515%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20facing%20an%20issue%20where%20the%20VBA%20script%20is%20not%20refreshing%20any%20pivot%20tables%20where%20I%20selected%20%22%20add%20this%20data%20to%20the%20data%20model%20%22%20when%20creating%20because%20I%20needed%20to%20use%20the%20Distinct%20Count%20Function.%20My%20question%20is%20if%20anybody%20know%20how%20I%20can%20use%20a%20Distinct%20count%20function%20without%20having%20to%20select%20%22add%20this%20data%20to%20the%20data%20model%22%20when%20creating%20the%20pivot%20table%2C%20or%20a%20VBA%20script%20that%20would%20not%20give%20me%20any%20errors%20when%20refreshing%20the%20pivot%20tables.%20This%20is%20my%20Script%3A%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20RefreshAllPivotTables()%3C%2FP%3E%3CP%3EDim%20PT%20As%20PivotTable%3CBR%20%2F%3EDim%20WS%20As%20Worksheet%3C%2FP%3E%3CP%3EFor%20Each%20WS%20In%20ThisWorkbook.Worksheets%3C%2FP%3E%3CP%3EFor%20Each%20PT%20In%20WS.PivotTables%3CBR%20%2F%3E%3CFONT%20color%3D%22%23DF0000%22%3EPT.RefreshTable%26nbsp%3B%20%3CFONT%20color%3D%22%23000000%22%3Eit%20usually%20gives%20me%20error%20'1004'%20here%26nbsp%3B%3C%2FFONT%3E%3C%2FFONT%3E%3CBR%20%2F%3ENext%20PT%3C%2FP%3E%3CP%3ENext%20WS%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20when%20I%20delete%20all%20pivot%20tables%20that%20use%20a%20distinct%20count%20function%20it%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3111515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3117495%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Script%20not%20Refreshing%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3117495%22%20slang%3D%22en-US%22%3EOdd%2C%20it%20works%20for%20me!%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

 

1 Reply
Odd, it works for me!