使用VBA -通配符过滤器更新和过滤枢轴

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.

我希望清除数据透视字段Invoicenr上的prevoius过滤器，更新一个pivot表，并且不显示某些项。我想要展示所有的东西，但是有一个从PO*开始的Invoicenr的项目(似乎在VBA中不能使用*)。除此之外，我还想看看其他的东西和以PO开头并包含OH的Invoicenr。

See my attempt below:

看到我尝试如下:

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

2 个解决方案

#1

Use this code:

使用这段代码:

   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

Then make 2 command buttons with this code

然后用此代码创建两个命令按钮

filtering All EXCEPT PO

过滤除了阿宝

    Private Sub CommandButton1_Click()
       Call Except_PO

     End Sub

Filtering data starting with PO and contains OH

从PO开始过滤数据，包含OH

   Private Sub CommandButton2_Click()
       Call POwithOH

     End Sub

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.

所以如果你点击CommandButton1，你的主元会过滤掉那些不从PO开始的数据。当您单击CommandButton2时，您的pivot将过滤所有以PO开头并包含OH的数据。

#2

If I'm understanding the conditions correctly, this should get you the results you want for the first case...

如果我理解正确的话，这应该能让你得到你想要的第一种情况的结果……

Show All Items except ones that begin with "PO" :

显示除以“PO”开头的项目外的所有项目:

Sub ShowAllButPO()

    Dim ws As Worksheet
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem

    Set ws = ActiveSheet
    Set pvtTable = ws.PivotTables("PIVOT1")
    Set pvtField = pvtTable.PivotFields("Invoicenr")

    pvtTable.RefreshTable

    pvtTable.ClearAllFilters

    For Each pvtItem In pvtField.PivotItems
        If Left(UCase(pvtItem), 2) = "PO" Then
            pvtItem.Visible = False
        End If
    Next

End Sub

And this should cover the second condition...

这应该包括第二个条件……

Show All Items in "invoicenr" that start with "PO" and also contain "OH" :

显示“invoicenr”中以“PO”开头并包含“OH”的所有项目:

Sub ShowOnlyPO()

    Dim ws As Worksheet
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem

    Set ws = ActiveSheet
    Set pvtTable = ws.PivotTables("PIVOT1")
    Set pvtField = pvtTable.PivotFields("Invoicenr")

    pvtTable.RefreshTable

    pvtTable.ClearAllFilters

    For Each pvtItem In pvtField.PivotItems
        If Left(UCase(pvtItem), 2) = "PO" And InStr(UCase(pvtItem), "OH") > 0 Then
            pvtItem.Visible = True
        Else
            pvtItem.Visible = False
        End If
    Next

End Sub

I'm less sure about what you wanted for the second condition. Your wording "i want to see Everything else and the invoicenr that starts with PO and contains "OH"" wasn't completely clear to me.

我不太确定你对第二个条件想要什么。你说的“我想看看其他的东西，发票以PO开头，包含“OH”，我不是很清楚。

If you could clarify what you mean by "Everything else and invoicenr that starts with PO.. etc etc" then I can update my code if needed.

如果你能阐明你所说的“其他一切和以PO开头的发票”是什么意思。等等，如果需要的话，我可以更新我的代码。

Also, if those two code blocks end up getting you what you want, then you could just assign each macro to its own button in your worksheet. That way, you could just toggle the two scenarios without having to open the VBEditor to run the code. If you are unsure how to do this, check out this link

另外，如果这两个代码块最终得到了您想要的结果，那么您可以在工作表中为每个宏分配它自己的按钮。这样，您只需切换这两个场景，而不必打开VBEditor来运行代码。如果你不知道怎么做，看看这个链接

#1