I have tried to create a macro to add a measure to all the pivot tables in my workbook but cannot get the
syntax correct.
We are running on Microsoft Office Home and Business 2016+
I have managed to add a measure to all the pivot Charts. See macro code attached
syntax correct.
We are running on Microsoft Office Home and Business 2016+
I have managed to add a measure to all the pivot Charts. See macro code attached
Sub ChangePivot()
' CHANGE AlL PIVOTS IN THE WORKSHEETS
NewCost = Worksheets("Current - Group and Company").Range("T4").Value
Dim ws As Worksheet, pc As ChartObject
For Each ws In ThisWorkbook.Worksheets
For Each pc In ws.ChartObjects
MsgBox pc.Chart.Name & String$(2, vbLf) & pc.Name, pc.ProtectChartObject
For Each CBField In pc.Chart.PivotLayout.PivotTable.CubeFields
If CBField.CubeFieldType = xlMeasure Then
CBField.Orientation = xlHidden
End If
Next CBField
Select Case NewCost
Case "Standard"
pc.Chart.PivotLayout.PivotTable.AddDataField pc.Chart.PivotLayout. _
PivotTable.CubeFields("[Measures].[Total_Std_Cost]")
Case "Average"
pc.Chart.PivotLayout.PivotTable.AddDataField pc.Chart.PivotLayout. _
PivotTable.CubeFields("[Measures].[Total_Average_Cost]")
Case "FIFO"
pc.Chart.PivotLayout.PivotTable.AddDataField pc.Chart.PivotLayout. _
PivotTable.CubeFields("[Measures].[Total_FIFO_Cost]")
End Select
Next
Next
'Sub foo()
'Dim sh As Object, pc As Chart, pco As ChartObject
' For Each sh In ThisWorkbook.Sheets
' For Each pco In sh.ChartObjects
' MsgBox pco.Chart.Name & String$(2, vbLf) & pco.Name
'Next
' Next
' For Each pc In ThisWorkbook.Charts
' MsgBox pc.Name
' Next
'End Sub
'Dim sh As Object, pc As Chart, pco As ChartObject
'For Each objCubeFld In ActiveChart.PivotLayout.PivotTable.CubeFields
'ACTIVE CHART
'For Each CBField In ActiveChart.PivotLayout.PivotTable.CubeFields
'If CBField.CubeFieldType = xlMeasure Then
'CBField.Orientation = xlHidden
'End If
'Next CBField
'Select Case NewCost
'Case "Standard"
' ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
' PivotTable.CubeFields("[Measures].[Total_Std_Cost]")
'Case "Average"
'ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
'PivotTable.CubeFields("[Measures].[Total_Ave_Cost]")
'Case "FIFO"
'ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
'PivotTable.CubeFields("[Measures].[Total_FIFO_Cost]")
'End Select
'ACTIVE CHART
End Sub