I am having a problem with my pivot table's REPORT FILTER becoming un-alphabetized after I update and refresh the pivot table's source data. I am using the code below. In the past, I was able to remove and replace my report filters and this would remedy the issue (I am unable to do that in this case for whatever reason). What do I need to do so that the report filter re-alphabetizes the new data coming into the pivot table?
'REMOVE REPORT FILTERS
Sheets("PARS Trend").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("CM").Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("TSO").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("CPO").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = _
xlHidden
'UPDATE ALL PIVOT TABLES
Dim PT As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = "Data!" & Range("A1:Q50000").Address(True, True, xlR1C1, False)
PT.RefreshTable
Next PT
Next ws
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc
'REPLACE REPORT FILTERS
Sheets("PARS Trend").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CM")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TSO")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CPO")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
.Orientation = xlPageField
.Position = 1
End With
Thanks for your help!
~Plat
Bookmarks