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
– Max, Commented Sep 26, 2013 at 15:08
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
– Max, Commented Sep 27, 2013 at 20:11
it would be helpful to see if your question is answered (tick the answer), or if any Problems remain! — Max
– Max, Commented Oct 6, 2013 at 12:56

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

1

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

answered Oct 1, 2013 at 11:33

Max

7591 gold badge7 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GAB Over a year ago

Max, With pt.PivotFields("month") .Orientation = xlColumnField .Position = 1 Is that where it's updateing the Column Label? If my field says 'Created Month' I should have that in there? Thanks so much... this is a huge help!

Max Over a year ago

yes exactly, this is where to put the field Name. but also change it in pt.PivotFields("month").Orientation = xlHidden; in that part (to make sure non is left) week+month are removed from the columns before inserting the new column Label.

Tim Williams · Accepted Answer · 2013-09-25 23:10:31Z

0

You need at least one valid value in G3:G7 or this will error:

Dim pf As PivotField, pi As PivotItem

Set pf = ActiveSheet.PivotTables("Tickets by Group").PivotFields("Volume")
pf.ClearAllFilters
pf.EnableMultiplePageItems = True

For Each pi In pf.PivotItems
    pi.Visible = Not IsError(Application.Match(pi.Caption, Range("G3:G7"), 0))
Next pi

answered Sep 25, 2013 at 23:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

GAB Over a year ago

Thanks Tim, though I needed to know that but actually there is more into it: I'll edit my question!

Collectives™ on Stack Overflow

Filter Excel pivot table with multiple criteria using VBA

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related