Filter Excel pivot table with multiple criteria using VBA

Question

Edited

IF Select Screen Field (worksheet) Range N3 = Monthly,

I need Worksheets 'Tickets by Group', 'Top 10 Bill tos', 'Top 10 CSRs', Top 10 Categories', Top 10 Created by' to remove the value in the pivot table and then insert 'Created Month'. Then have all refresh.

IF Select Screen Field (worksheet) Range N3 = Weekly,

I need Worksheets 'Tickets by Group', 'Top 10 Bill tos', 'Top 10 CSRs', Top 10 Categories', Top 10 Created by' to remove the value in the pivot table and then insert 'Week Number'. Then have all refresh where also the only fields to show would be from Select Screen Range A2, B2, C2, D2, E2 and F2 which will never be blank.

There will never be blanks in any field that it would look at and those are the only 2 options for the Select Screen Field(Worksheet)

Max....

On the first sheet I am adding a drop down where either Monthly or Weekly can be selected. I have that value on the 'Select Screen Field' (worksheet). I then have 5 other worksheets that have pivot tables on them. If they select Monthly I would want the pivot table on each to clear out all filters and then clear out the column label and add Created Month... When running a macro and then looking at the results I get:

ActiveSheet.PivotTables("Volume").PivotFields("Week Number").Orientation = _ xlHidden With ActiveSheet.PivotTables("Volume").PivotFields("Created Month") .Orientation = xlColumnField .Position = 1

But when Weekly is selected I need it to do the same but instead to show 'Week Number' and then have the Pivot table filter to only showing 6 weeks. That value will be based off 'Select Screen Field', A2, B2, C2, D2, E2 and F2. Which currently shows, 39, 38, 37, 36, 35, 34.

your question is not clear at all - what date to you have, what are you trying to do, what should be the outcome? — Max
getting a little clearer.... So in cell "N3" you have either "monthly" or "weekly" and in each of the other mentioned sheets you have Pivot tables you want to change - right so far? What exactlyx is to be changed, you can not remove values in Pivot tables. You want to Change the filter or columns or...? And you only want to show CELLS A2:F2 or columns A:F - and whats the aim of that? — Max
it would be helpful to see if your question is answered (tick the answer), or if any Problems remain! — Max

Max Max · Accepted Answer · 2013-10-01T11:33:11

I think we have it now... if the pivottablename is not "volume" in one of the Sheets, change that. the week filter is not searching all of the mentioned fields, but filtering weeks greater/equal A2

Sub start()
Call pivotchange("Tickets by Group", "Volume")
Call pivotchange("Top 10 Bill tos", "Volume")
Call pivotchange("Top 10 CSRs", "Volume")
Call pivotchange("Top 10 Categories", "Volume")
Call pivotchange("Top 10 Created by' ", "Volume")
End Sub

Sub pivotchange(sheetname As String, pivottablename As String)
On Error Resume Next
Dim week_filter As String
Dim pt As PivotTable
Set pt = Sheets(sheetname).PivotTables(pivottablename)
    pt.ClearAllFilters
    pt.PivotFields("week").Orientation = xlHidden
    pt.PivotFields("month").Orientation = xlHidden

If Sheets("Select Screen Field").Range("N3").Value = "weekly" Then
    With pt.PivotFields("week")
        .Orientation = xlColumnField
        .Position = 1
    End With
week_filter = Sheets("Select Screen Field").Range("A2").Value
    pt.PivotFields("week").PivotFilters.Add _
        Type:=xlCaptionIsGreaterThanOrEqualTo, Value1:=week_filter
    pt.PivotFields("week").AutoSort xlAscending, "week"
End If
If Sheets("Select Screen Field").Range("N3").Value = "monthly" Then
    With pt.PivotFields("month")
        .Orientation = xlColumnField
        .Position = 1
    End With
    pt.PivotFields("week").AutoSort xlAscending, "month"
End If
pt.RefreshDataSourceValues
pt.RefreshTable
End Sub

Filter Excel pivot table with multiple criteria using VBA

2 Answers