Excel Pivot Table - How to preserve Filter and Sorting when Data get refreshed

Question

I have a pivot table (Excel 2010). Every Time when I update the data and make a refresh, the sorting and filters of the Pivot Table get lost repsectively I need to explicitely to modify the filter (select all, and remove the ones which are not needed).

Any solution for this?

• How can I change the filter through VBA when the pivot table get refreshed?
• How can I change the sorting through VBA when the pivot table get refreshed?
• How can I trigger a refresh of the pivot table when the data changes?

Thank you in advance regards Mark

Answer 1

I could achieve what I wanted. I simple recorded first a Macro in order to see the details which objects have to be how accessed. Afterwards I finished the thing by making some optimization. Please refer below to the code extract, maybe helpful for others.

Regarding the ordering I found in a forum the tip that you should assing another column "sort order" in the origin data table and used it as hidden column in the pivot table so you can define the sort order by using that field. This one get also not lost when you later change the data.

Sub Macro1()
On Error GoTo Fehler
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Rating").PivotTables(1)
.PivotFields("OWASP 2010").ClearAllFilters
With .PivotFields("OWASP 2010")

    If .PivotItems("(blank)").Visible = True Then
        .PivotItems("(blank)").Visible = False
    End If
    ' uncomment this sectin in case you liek to change the sorting via vba
    ' 1. sort first the stuff automatically A-Z
    '.AutoSort xlAscending, "OWASP 2010"
    ' 2. move empty values to the end
    'If .PivotItems(" ").Position <> .PivotItems.Count - 1 Then
    '    .PivotItems(" ").Position = .PivotItems.Count - 1
    'End If
 End With
.PivotFields("Categories").ClearAllFilters
With .PivotFields("Fortify Categories")
     If .PivotItems("0").Visible = True Then
        .PivotItems("0").Visible = False
     End If
     If .PivotItems("(blank)").Visible = True Then
        .PivotItems("(blank)").Visible = False
    End If
End With
End With
Application.ScreenUpdating = True
Exit Sub
Fehler:
Application.ScreenUpdating = True
End Sub

regards Mark