使用VBA更新和过滤数据透视表-通配符过滤器 [英] Update and filter pivot with VBA - Wildcard filter

问题描述

我想清除数据透视表 Invoicenr 上的prevoius过滤器，更新数据透视表，但不显示某些项目.
我想显示除具有以 PO * 开头的 Invoicenr 的项目外的所有内容(似乎*无法在VBA中使用?).
除此之外，我还想查看其他所有内容以及以 PO 开头并包含 OH 的 Invoicenr .

I want to clear the prevoius filter on pivotfield Invoicenr, update a pivot table, and not show certain items.
I want to show Everything but the items that have a Invoicenr that begins with PO* (seems that * can't be used in VBA?).
Besides this I want to see everything else and the Invoicenr that starts with PO and contains OH.

请在下面查看我的尝试

Sub Macro2()
'
' Macro2 Macro
'
    ThisWorkbook.RefreshAll    
      'Worksheets("Pivot").Select
      'ActiveSheet.PivotTables("PIVOT1").RepeatAllLabels xlRepeatLabels
    ActiveSheet.PivotTables("PIVOT1").PivotFields("Invoicenr"). _
        ClearLabelFilters              
    With ActiveSheet.PivotTables("PIVOT1").PivotFields("invoicenr")
        .PivotItems("PO").Visible = False         
    End With        
End Sub

推荐答案

使用以下代码:

   Sub Except_PO()

      Dim var As Variant

      var = "PO*"

     ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").ClearAllFilters
    ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").PivotFilters. _
    Add Type:=xlCaptionDoesNotEqual, Value1:=var

   End Sub


  Sub POwithOH()

   Dim var As Variant

    var = "PO*OH*"

    ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").ClearAllFilters
    ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").PivotFilters. _
      Add Type:=xlCaptionEquals, Value1:=var


  End Sub

然后使用此代码制作2个命令按钮

Then make 2 command buttons with this code

过滤除PO外的所有

    Private Sub CommandButton1_Click()
       Call Except_PO

     End Sub

过滤以PO开头且包含OH的数据

Filtering data starting with PO and contains OH

   Private Sub CommandButton2_Click()
       Call POwithOH

     End Sub

因此，如果您单击CommandButton1，则数据透视表将过滤那些不是以PO开头的数据.然后，当您单击CommandButton2时，您的数据透视表将过滤所有以PO开头且包含OH的数据.

So if you click CommandButton1, your pivot will filter those data that don't start with PO. And when you click CommandButton2, your pivot will filter all data that starts with PO AND contains OH.

这篇关于使用VBA更新和过滤数据透视表-通配符过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！