Confirm Pivot Table structure, Refresh, VBA

[Rowland Hamilton]

Folks:

How do I make sure the field order is corrected if someone moves the fields around before refresh? Right now, I can't use the position commands because the fields already exist. I thought the remove fields code would work but its only removing data fields. - Thank you, Rowland

Sub Confirm_Pivot()
'
        PivotReport.Activate
        
        Remove_All_Pivot_Data_Fields
        
        PivotReport.PivotTables("AgingPivot").AddDataField PivotReport.PivotTables( _
            "AgingPivot").PivotFields("Capture"), "Sum of Capture", xlSum
            
        With PivotReport.PivotTables("AgingPivot")
            .PivotCache.Refresh
            .PivotFields("Group").Orientation = xlColumnField '.Position = 1
            .PivotFields("Service Type*").Orientation = xlRowField '.Position = 1
            .PivotFields("Priority").Orientation = xlRowField '.Position = 2
            
            .PivotFields("Service Type*").PivotItems("Infrastructure Restoration").Visible = False
            .PivotSelect "'Service Type*'[All;Total]", xlDataAndLabel, True
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                With Selection
                    .Font.Bold = True
                End With
            .PivotFields("Group").CalculatedItems("Total Outstanding").Delete
            .PivotFields("Group").CalculatedItems.Add "Total Outstanding", _
                "='Age<=30' +'30<Age<=60' +'60<Age<=90' +'Age>90'", True
            .PivotSelect "Group['Resolved w/in 30 Days','Age<=30','30<Age<=60','60<Age<=90','Age>90'," & _
                         "'Total Outstanding']", xlDataAndLabel, True
                        With Selection
                            .HorizontalAlignment = xlCenter
                        End With
        End With
        
   End Sub

Private Sub Remove_All_Pivot_Data_Fields()
Dim PT As PivotTable, ptField As PivotField, ptItem As PivotItem
Set PT = PivotReport.PivotTables("AgingPivot")
For Each ptField In PT.DataFields
    On Error GoTo CalcField
    ptField.Orientation = xlHidden
    On Error GoTo 0
Next ptField
Set PT = Nothing
Exit Sub

CalcField:
Set ptItem = ptField.DataRange(1).PivotItem
ptItem.Visible = False
Set ptItem = Nothing
Resume Next

End Sub

 

[Rowland Hamilton]

Sample file: Aging Report-Dummy Data v4.zip = sample file