I have a spreadsheet and I created a sub function (please see below) to make a call to update specific pivot tables. One particular pivot table (“DataModel”) has text as values, so, when I created that pivot table I added it to the data model then did a “Add Measure” and created a line code “=CONCATENATEX(DataModel,[DataValue],”, “)” so I can pivot on the text value.
Anyway, when I run the code below, it updates all the pivot tables I want except for the one made from the data model. Does anyone know how I can use Excel VBA to automate the pivot table made from the data model?
—- Call to module —
—- Excel VBA Code —
Public Sub RefreshPivots(ByVal sheetName As String)
Dim sheetExists As Boolean
sheetExists = False For Each ws In Worksheets If ws.Name = sheetName Then sheetExists = True Next ws If sheetExists Then Sheets(sheetName).Visible = True Sheets(sheetName).Activate For Each pivotTab In ActiveSheet.PivotTables pivotTab.PivotCache.Refresh Next pivotTab End If