(Solved) – Excel VBA auto refresh pivot made off of Data Model

(solved)-–-excel-vba-auto-refresh-pivot-made-off-of-data-model

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?

Thanks,
Mike
—- Call to module —

Call RefreshPivots(“DataModel”)

—- 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

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *