使用 VBA 过滤 Excel 数据透视表

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/11071662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 13:19:53  来源:igfitidea点击:

Filter Excel pivot table using VBA

excel-vbafilterpivot-tablevbaexcel

提问by user1283776

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA. The code below doesn't work.

我已经尝试从互联网上永远复制和粘贴解决方案,以尝试使用 VBA 在 Excel 中过滤数据透视表。下面的代码不起作用。

Sub FilterPivotTable()
    Application.ScreenUpdating = False
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True
    ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").CurrentPage = "K123223"
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
    Application.ScreenUpdating = True
End Sub

I want to filter so I see all rows that have SavedFamilyCode K123223. I don't want to see any other rows in the pivot table. I want this to work regardless of the previous filters. I hope you can help me with this. Thanks!

我想过滤,以便看到所有包含 SavedFamilyCode K123223 的行。我不想在数据透视表中看到任何其他行。无论以前的过滤器如何,我都希望它可以工作。我希望你能帮我解决这个问题。谢谢!



Based on your post I'm trying:

根据您的帖子,我正在尝试:

Sub FilterPivotField()
    Dim Field As PivotField
    Field = ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode")
    Value = Range("$A")
    Application.ScreenUpdating = False
    With Field
        If .Orientation = xlPageField Then
            .CurrentPage = Value
        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then
            Dim i As Long
            On Error Resume Next ' Needed to avoid getting errors when manipulating fields that were deleted from the data source.
            ' Set first item to Visible to avoid getting no visible items while working
            .PivotItems(1).Visible = True
            For i = 2 To Field.PivotItems.Count
                If .PivotItems(i).Name = Value Then _
                    .PivotItems(i).Visible = True Else _
                    .PivotItems(i).Visible = False
            Next i
            If .PivotItems(1).Name = Value Then _
                .PivotItems(1).Visible = True Else _
                .PivotItems(1).Visible = False
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Unfortunately I get Run time error 91: Object variable or With block variable not set. What has caused this error?

不幸的是,我收到运行时错误 91:未设置对象变量或块变量。是什么导致了这个错误?

回答by savyuk

Field.CurrentPage only works for Filter fields (also called page fields).
If you want to filter a row/column field, you have to cycle through the individual items, like so:

Field.CurrentPage 仅适用于过滤器字段(也称为页面字段)。
如果要过滤行/列字段,则必须循环浏览各个项目,如下所示:

Sub FilterPivotField(Field As PivotField, Value)
    Application.ScreenUpdating = False
    With Field
        If .Orientation = xlPageField Then
            .CurrentPage = Value
        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then
            Dim i As Long
            On Error Resume Next ' Needed to avoid getting errors when manipulating PivotItems that were deleted from the data source.
            ' Set first item to Visible to avoid getting no visible items while working
            .PivotItems(1).Visible = True
            For i = 2 To Field.PivotItems.Count
                If .PivotItems(i).Name = Value Then _
                    .PivotItems(i).Visible = True Else _
                    .PivotItems(i).Visible = False
            Next i
            If .PivotItems(1).Name = Value Then _
                .PivotItems(1).Visible = True Else _
                .PivotItems(1).Visible = False
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Then, you would just call:

然后,您只需调用:

FilterPivotField ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode"), "K123223"

Naturally, this gets slower the more there are individual different items in the field. You can also use SourceName instead of Name if that suits your needs better.

自然地,该领域中的单个不同项目越多,这会变得越慢。如果更适合您的需要,您也可以使用 SourceName 而不是 Name。

回答by whytheq

Configure the pivot table so that it is like this:

配置数据透视表,使其如下所示:

enter image description here

在此处输入图片说明

Your code can simply work on range("B1") now and the pivot table will be filtered to you required SavedFamilyCode

您的代码现在可以简单地处理 range("B1") 并且数据透视表将被过滤到您需要的 SavedFamilyCode

Sub FilterPivotTable()
Application.ScreenUpdating = False
    ActiveSheet.Range("B1") = "K123224"
Application.ScreenUpdating = True
End Sub

回答by MAF

You could check this if you like. :)

如果你愿意,你可以检查这个。:)

Use this code if SavedFamilyCode is in the Report Filter:

如果 SavedFamilyCode 在报告过滤器中,请使用此代码:

 Sub FilterPivotTable()
   Application.ScreenUpdating = False
   ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True

   ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").ClearAllFilters

   ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").CurrentPage = _
      "K123223"

  ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

But if the SavedFamilyCode is in the Column or Row Labels use this code:

但如果 SavedFamilyCode 在列或行标签中,请使用以下代码:

 Sub FilterPivotTable()
     Application.ScreenUpdating = False
     ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True

      ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").ClearAllFilters

      ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:="K123223"

  ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

Hope this helps you.

希望这对你有帮助。

回答by Brettvenker

I think i am understanding your question. This filters things that are in the column labels or the row labels. The last 2 sections of the code is what you want but im pasting everything so that you can see exactly how It runs start to finish with everything thats defined etc. I definitely took some of this code from other sites fyi.

我想我理解你的问题。这会过滤列标签或行标签中的内容。代码的最后 2 部分是您想要的,但我粘贴了所有内容,以便您可以确切地看到它如何运行,以及定义的所有内容等。我绝对从其他站点获取了一些代码仅供参考。

Near the end of the code, the "WardClinic_Category" is a column of my data and in the column label of the pivot table. Same for the IVUDDCIndicator (its a column in my data but in the row label of the pivot table).

在代码末尾附近,“WardClinic_Category”是我的数据列和数据透视表的列标签。IVUDDCIndicator 相同(它是我数据中的一列,但在数据透视表的行标签中)。

Hope this helps others...i found it very difficult to find code that did this the "proper way" rather than using code similar to the macro recorder.

希望这对其他人有帮助...我发现很难找到以“正确方式”执行此操作的代码,而不是使用类似于宏记录器的代码。

Sub CreatingPivotTableNewData()


'Creating pivot table
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField

'determine the worksheet which contains the source data
Set wsData = Worksheets("Raw_Data")

'determine the worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets("3N3E")

'delete all existing Pivot Tables in the worksheet
'in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl


'A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.

'set source data range:
Worksheets("Raw_Data").Activate
Set rngData = wsData.Range(Range("A1"), Range("H1").End(xlDown))


'Creates Pivot Cache and PivotTable:
Worksheets("Raw_Data").Activate
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData.Address, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")

'Default value of ManualUpdate property is False so a PivotTable report is recalculated automatically on each change.
'Turn this off (turn to true) to speed up code.
PvtTbl.ManualUpdate = True


'Adds row and columns for pivot table
PvtTbl.AddFields RowFields:="VerifyHr", ColumnFields:=Array("WardClinic_Category", "IVUDDCIndicator")

'Add item to the Report Filter
PvtTbl.PivotFields("DayOfWeek").Orientation = xlPageField


'set data field - specifically change orientation to a data field and set its function property:
With PvtTbl.PivotFields("TotalVerified")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "0.0"
.Position = 1
End With

'Removes details in the pivot table for each item
Worksheets("3N3E").PivotTables("PivotTable1").PivotFields("WardClinic_Category").ShowDetail = False

'Removes pivot items from pivot table except those cases defined below (by looping through)
For Each PivotItem In PvtTbl.PivotFields("WardClinic_Category").PivotItems
    Select Case PivotItem.Name
        Case "3N3E"
            PivotItem.Visible = True
        Case Else
            PivotItem.Visible = False
        End Select
    Next PivotItem


'Removes pivot items from pivot table except those cases defined below (by looping through)
For Each PivotItem In PvtTbl.PivotFields("IVUDDCIndicator").PivotItems
    Select Case PivotItem.Name
        Case "UD", "IV"
            PivotItem.Visible = True
        Case Else
            PivotItem.Visible = False
        End Select
    Next PivotItem

'turn on automatic update / calculation in the Pivot Table
PvtTbl.ManualUpdate = False


End Sub

回答by user3357094

Latest versions of Excel has a new tool called Slicers. Using slicers in VBA is actually more reliable that .CurrentPage (there have been reports of bugs while looping through numerous filter options). Here is a simple example of how you can select a slicer item (remember to deselect all the non-relevant slicer values):

最新版本的 Excel 有一个名为切片器的新工具。在 VBA 中使用切片器实际上比 .CurrentPage 更可靠(在循环浏览众多过滤器选项时已经报告了错误)。这是一个如何选择切片器项目的简单示例(记住取消选择所有不相关的切片器值):

Sub Step_Thru_SlicerItems2()
Dim slItem As SlicerItem
Dim i As Long
Dim searchName as string

Application.ScreenUpdating = False
searchName="Value1"

    For Each slItem In .VisibleSlicerItems
        If slItem.Name <> .SlicerItems(1).Name Then _
            slItem.Selected = False
        Else
            slItem.Selected = True
        End if
    Next slItem
End Sub

There are also services like SmartKato that would help you out with setting up your dashboards or reports and/or fix your code.

还有像 SmartKato 这样的服务可以帮助您设置仪表板或报告和/或修复您的代码。

回答by ghostJago

In Excel 2007 onwards, you can use the much simpler code using a more precise reference:

在 Excel 2007 以后,您可以使用更精确的参考使用更简单的代码:

dim pvt as PivotTable
dim pvtField as PivotField

set pvt = ActiveSheet.PivotTables("PivotTable2")
set pvtField = pvt.PivotFields("SavedFamilyCode")

pvtField.PivotFilters.Add xlCaptionEquals, Value1:= "K123223"