Pivot Tables - removing then adding fields using vba

[keiranwyllie]

Hi all,

I have a pivot table that is part of a data model (to ensure when filtering, calculations don't change). The following code was tested on a replica of the pivot table that is not part of a data model and it works 100% as I expect it to.

Sub updatePivotTable()

    On Error Resume Next
    On Error GoTo 0
    
    'Create new pivot table
    Dim wsData, wsPvt As Worksheet
    Set wsData = Worksheets("ISM Controls")
    Set wsPvt = Worksheets("Calculations")
    
    Dim lastCol As Long
    lastCol = wsData.Cells(1, Columns.Count).End(xlToLeft).Column
        
    Dim dataRange As Range
    Dim pvtField As String
    Set dataRange = Range("GuidelineData")
    Dim lastCol2 As Long
    Dim col As Long
    For col = dataRange.Columns.Count To 1 Step -1
        If InStr(1, dataRange.Cells(1, col).Value, "Implementation Status", vbTextCompare) > 0 Then
            lastCol2 = col
            pvtField = wsData.Cells(1, lastCol2).Value
            Exit For
        End If
    Next col
    
    Dim pt As PivotTable
    Set pt = wsPvt.PivotTables("pvtGuidelines")
    pt.RefreshTable
        
    Dim pf As PivotField
    For Each pf In pt.PivotFields                                                ' This will remove the "Implementation Status" column
        If pf.Orientation = xlColumnField Then
            pf.Orientation = xlHidden
        End If
    Next pf
    
    Dim df As PivotField
    If pt.DataFields.Count > 0 Then
        For Each df In pt.DataFields                                           ' This will remove the "Count of Implementation Status" value
            df.Orientation = xlHidden
        Next df
    End If
    
    With pt
        'Set pivot table fields
        .AddFields RowFields:="Guideline", ColumnFields:=pvtField
        .PivotFields(pvtField).Orientation = xlDataField
        .PivotFields("Count of " & pvtField).Calculation = xlPercentOfRow
        .PivotFields("Count of " & pvtField).NumberFormat = "0%"
        'Format pivot table
        .ColumnGrand = False
        .RowGrand = True
    End With
    
End Sub

When I use this code in the spreadsheet with the data model, I get errors. The first is when trying to remove the Column Field "Implementation Status" which gives the error "Unable to set the Orientation property of the PivotField class."

I'm confident the issue is because of the data table but I'm truly lost on how to fix it. I've not had much luck searching the forum or web for solutions.

Any help would be appreciated.

 

[keiranwyllie]

Update.

I've had some success now that I'm using CubeFields instead of PivotFiels however the issue I now have is how to modify the syntax so that I can use my string 'pvtField' in place of 'Implementation Status'

One piece of code within the last with statement is as follows:

.CubeFields("[GuidelinesData].[Implementation Status]".Orientation = xlColumnField

I want to replace [Implementation Status] with [pvtField] however I just keep getting an error thrown at me.

What's the best way to modify the reference inside [ ] to work with a variable?