Posts by AnsieB

    I want to change the measures of all pivottables in the sheet/workbook using a macro.


    I have successfully changed all the charts in the Workbook using the following code.


    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