Confirm Pivot Table structure, Refresh, VBA

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
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
Code:
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,579
Messages
6,173,173
Members
452,505
Latest member
Alice34

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top