使用VBA筛选具有多个条件的Excel数据透视表 [英] Filter Excel pivot table with multiple criteria using VBA

问题描述

已编辑

如果选择屏幕字段(工作表)，范围N3 =每月，

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

我需要工作表按组划分的票"，前10个帐单"，前10个企业社会责任"，前10个类别，创建的前10个"，以删除数据透视表中的值，然后插入创建月份" .然后刷新一下.

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.

如果选择屏幕字段(工作表)，范围N3 =每周，

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

我需要工作表按组划分的票"，前10个帐单"，前10个企业社会责任"，前10个类别，创建的前10个"，以删除数据透视表中的值，然后插入周数" .然后进行所有刷新，其中唯一要显示的字段也将来自选择屏幕范围A2"，"B2"，"C2"，"D2"，"E2"和"F2"，这些字段永远不会空白.

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.

在任何要查看的字段中都不会有空格，而这是选择屏幕字段(工作表)"的仅有2个选项

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)

最大...

在第一张纸上，我添加了一个下拉菜单，可以选择每月"或每周".我在选择屏幕字段"(工作表)上具有该值.然后，我还有其他5个工作表，上面有数据透视表.如果他们选择每月"，我希望每个数据透视表清除所有过滤器，然后清除列标签并添加创建的月份" ...运行宏，然后查看结果，我得到:

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 = _ 隐藏 使用ActiveSheet.PivotTables("Volume").PivotFields("Created Month") .Orientation = xlColumnField .Position = 1

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

但是，在选择每周当我需要它做同样的，而是显示周的数目"，然后有透视表滤波器以只示出6个星期.该值将基于选择屏幕字段"，A2，B2，C2，D2，E2和F2.当前显示的是39、38、37、36、35、34.

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.

推荐答案

我认为我们现在有了它...如果在其中一个Sheets中，pivottablename不是"volume"，则进行更改. 周过滤器不是搜索所有提到的字段，而是过滤周大于/等于A2的

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

这篇关于使用VBA筛选具有多个条件的Excel数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！