组合框更改excel数据透视表上的过滤器 [英] combo box to change filters on excel pivot table

问题描述

Excel 2013.我使用3个组合框更改数据透视表上的过滤器。
我的第一个组合框有Project1，Project2&所有。
我的第二个组合框有Customer1，Customer2&所有。
我的第三个组合框有Country1，Country2&所有。

我使用9个数据透视表，它们都有过滤器，如[Project]，[Customer]，[Country]。

我的意图是将第一个组合框更改为Project1&所有的枢轴表过滤器应该更改为Project1.I我成功地能够这样做。

但是，当我选择第一个组合框为全部时。第一个组合框单元链接到Y1。我得到VBA运行时错误1004：应用程序定义或对象定义的错误。

  Sub ProjectName（）
 
 ActiveSheet.PivotTables（PVT1）。PivotFields（Project Name）。ClearAllFilters 
 ActiveSheet.PivotTables（PVT2）。PivotFields（Project Name）。ClearAllFilters 
 ActiveSheet.PivotTables （PVT3）。PivotFields（Project Name）。ClearAllFilters 
 
 ActiveSheet.PivotTables（PVT1）。PivotFields（Project Name）。CurrentPage = Range 
 ActiveSheet.PivotTables（PVT3）。PivotFields（Project Name）CurrentPage = Range（Y1）。 ）.CurrentPage = Range（Y1）。文本
  

解决方案

由于前三行代码没有问题，我将假设数据透视表 PVT1 和字段项目名称都存在。

对于 .CurrentPage 的调用，您将得到一个1004错误以下原因：

• 使用此工具尝试过滤未设置为报表过滤器的任何字段。您不能使用 CurrentPage 过滤任何行或列


• 设置可能值列表中不存在的值
• 
  

在第二点，这是调用 Range 可能是相关的。

• 验证该列表中是否存在可能的值。


• 正在使用 .Text ，它将使用单元格的 display 值，而不是其底层 .Value

要解决这些问题，有以下几种选择：

• 对于要过滤行或列（而不是过滤器部分）中的数据的情况，您可以通过 PivotItems 并设置 Visible = True / False


• 您还可以从VBA中设置一个标签过滤器，


• 如果要检查 CurrentPage 中是否存在值，可以重复 PivotItems 为 PivotField 并检查一个匹配。这个代码非常类似于 For Each 循环与检查值，只是不设置可见



代码。

  Sub FilterPivotField（）
 
 Dim pt As PivotTable 
设置pt = ActiveSheet.PivotTables（PVT1）
 
 Dim pf As PivotField 
设置pf = pt.PivotFields（C）
 
 pf.ClearAllFilters 
 
'慢遍历所有项目并设置可见（手动过滤器）
 Dim pi As PivotItem 
对于每个pi在pf.PivotItems 
 pi.Visible =（pi.Name = Range（J2））
下一个
 
'fast way设置一个标签过滤器
 pf.PivotFilters.Add2类型：= xlCaptionEquals，Value1：= Range（J2）
 
 End Sub 
 

范围图片

Excel 2013. I am using 3 combo boxes to change filters on the pivot table. My first combo box has "Project1", "Project2" & All. My second combo box has "Customer1", "Customer2" & All. My third combo box has "Country1", "Country2" & All.

I am using 9 pivot tables, all of them have filters as [Project], [Customer], [Country].

My intention is to change first combo box to Project1 & all the pivot tables filter should change as Project1.I am successfully able to do that.

However when I select the first combo box as "All". First Combo box cell link to Y1. I get VBA Run time error 1004: Application-defined or object-defined error.

Sub ProjectName()

ActiveSheet.PivotTables("PVT1").PivotFields("Project Name").ClearAllFilters
ActiveSheet.PivotTables("PVT2").PivotFields("Project Name").ClearAllFilters
ActiveSheet.PivotTables("PVT3").PivotFields("Project Name").ClearAllFilters

    ActiveSheet.PivotTables("PVT1").PivotFields("Project Name").CurrentPage = Range("Y1").Text
    ActiveSheet.PivotTables("PVT2").PivotFields("Project Name").CurrentPage = Range("Y1").Text
    ActiveSheet.PivotTables("PVT3").PivotFields("Project Name").CurrentPage = Range("Y1").Text 

解决方案

Since the first three lines of code go without issue, I will assume that the Pivot Table PVT1 and the Field Project Name all exist. This places the error somewhere after that.

For the call to .CurrentPage you will get a 1004 error for the following reasons:

• Using this to try and filter any field that is not set as a Report Filter. You cannot use the CurrentPage to filter any rows or columns
• Setting a value which does not exist in the list of possible values

On the second point, this is where the call to Range might be relevant.

• Verify that the value there exists in the list of possible ones.
• Also be aware you are using .Text which will use the display value of the cell and not its underlying .Value

To resolve these issues, there are a couple of options:

• For the case where you want to filter data that is on the row or column (and not in the filters section) you can go through PivotItems and set Visible = True/False
• You can also set a label filter from VBA if you want that instead of the manual filter
• If you want to check for a value existing in the CurrentPage, you can iterate the PivotItems for that PivotField and check that one matches. The code for that is very similar to the For Each loop with the check on value, just don't set Visible.

Code for setting a filter on a row or column

Sub FilterPivotField()

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("PVT1")

    Dim pf As PivotField
    Set pf = pt.PivotFields("C")

    pf.ClearAllFilters

    'slow iterates all items and sets Visible (manual filter)
    Dim pi As PivotItem
    For Each pi In pf.PivotItems
        pi.Visible = (pi.Name = Range("J2"))
    Next

    'fast way sets a label filter
    pf.PivotFilters.Add2 Type:=xlCaptionEquals, Value1:=Range("J2")

End Sub

Picture of ranges

这篇关于组合框更改excel数据透视表上的过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！