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