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.
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?
