有没有办法取消选中数据透视表中除特定项目之外的其他项目? [英] Is there a way to uncheck other items in a pivot table except a specific one?

问题描述

我是 VBA 新手，我在处理一些关于如何取消选中或取消选择数据透视表中除特定项目外的所有项目的代码时遇到了一些问题.

我正在创建一个按钮来自动过滤数据，这些数据只会显示一月(20190131")或二月(20190228")等等的数据.

我仍然没有 10 月(20191031")、11 月和 12 月的数据，这会导致错误无法设置 PivotItem 的 Visible 属性"，因为仍然没有这些日期的现有数据.我只想选中一个项目，其余的不选中.

我尝试手动将 Pivot Items 的可见性设置为 false，excel 需要很长时间才能取消选中它们，而且我不希望我的项目是静态的.而且，如果数据仍然不存在，例如我使用的数据仅从 2019 年 1 月到 2019 年 9 月.

 Sub Procdate_January()Procdate_January 宏' 用于更改工作表中数据透视表 PROCDATE 的宏工作表 1"到20190131".工作表(工作表 1").选择ActiveSheet.PivotTables("PivotTable1").PivotFields("PROCDATE")._CurrentPage = "(全部)"使用 ActiveSheet.PivotTables("PivotTable1").PivotFields("PROCDATE").PivotItems("20190131").Visible = True.PivotItems("20190228").Visible = False.PivotItems("20190331").Visible = False.PivotItems("20190430").Visible = False.PivotItems("20190531").Visible = False.PivotItems("20190630").Visible = False.PivotItems("20190731").Visible = False.PivotItems("20190831").Visible = False.PivotItems("20190930").Visible = False.PivotItems("20191031").Visible = False.PivotItems("20191130").Visible = False.PivotItems("20191231").Visible = FalseActiveSheet.PivotTables("PivotTable2").PivotFields("XDPI_PROCDATE")._CurrentPage = "(全部)"结束于结束子

我希望输出将是数据透视表中唯一可见的过滤器，它将是20190131".但是报错，找不到其他的20190930、20191031、20191130、20191231.

解决方案

如果只有一项要选择，则不要逐项设置，甚至循环设置.只需清除过滤器并将值设置为相关值即可.

看到这个

手动方式

通过 VBA 代码

选项显式子样本()Dim ws as 工作表将 PivName 调暗为字符串将 PivField 调暗为字符串Dim PivValue 作为字符串'~~>适用时更改设置 ws = Sheet1PivName = "数据透视表 1"PivField = "年" '~~>在你的情况下PROCDATE?PivValue = 2019 '~~>20190131 在你的情况下?使用 ws.PivotTables(PivName).PivotFields(PivField).清除所有过滤器.PivotFilters.Add2 类型:=xlCaptionEquals，Value1:=PivValue结束于结束子

<块引用>

无论如何我可以将PROCDATE"放在数据透视表的过滤器"部分而不是行"中吗?

录制宏时.你会得到这样的东西

ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ClearAllFiltersActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = "2014"

所以只需将上面的代码更改为

使用 ws.PivotTables(PivName).PivotFields(PivField).清除所有过滤器.CurrentPage = PivValue结束于

I am new to VBA and I am having some trouble with some code on how to uncheck or deselect all items in a pivot table except a specific one.

I am creating a button to automatically filter data that will only reveal data in January ("20190131") or in February ("20190228") and so on etc.

I still dont have data for October ("20191031"), November and December, which causes error "Unable to set the Visible property of the PivotItem" because there is still no existing data of those dates. I just want an item to be checked and the rest to be unchecked.

I tried manually setting the visibility of Pivot Items to false and it is taking a long time for excel to uncheck them and I do not want my items to be static. And also if data still is not existing for example the data I am using is only from January 2019 to September 2019.

   Sub Procdate_January()

  Procdate_January Macro
  ' Macro for changing the PROCDATE of Pivot Tables in Worksheet 
  "Worksheet1" to "20190131".

Sheets("Worksheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("PROCDATE"). _

    CurrentPage = "(All)"

With ActiveSheet.PivotTables("PivotTable1").PivotFields("PROCDATE")
    .PivotItems("20190131").Visible = True

    .PivotItems("20190228").Visible = False

    .PivotItems("20190331").Visible = False

    .PivotItems("20190430").Visible = False

    .PivotItems("20190531").Visible = False

    .PivotItems("20190630").Visible = False

    .PivotItems("20190731").Visible = False

    .PivotItems("20190831").Visible = False

    .PivotItems("20190930").Visible = False

    .PivotItems("20191031").Visible = False

    .PivotItems("20191130").Visible = False

    .PivotItems("20191231").Visible = False

    ActiveSheet.PivotTables("PivotTable2").PivotFields("XDPI_PROCDATE"). _
    CurrentPage = "(All)"
    End With
End Sub

I expect that the output will be the only visible filter in the pivot table will be "20190131". But it gets an error that the other 20190930, 20191031, 20191130, 20191231 cannot be found.

解决方案

If there is only one item to select then DO NOT set each item one by one or even in a loop. Simply clear the filters and set the value to the relevant value.

See this

Manual Way

Via VBA Code

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim PivName As String
    Dim PivField As String
    Dim PivValue As String

    '~~> Change as applicable
    Set ws = Sheet1

    PivName = "PivotTable1"
    PivField = "Year"    '~~> PROCDATE in your case?
    PivValue = 2019      '~~> 20190131 in your case?

    With ws.PivotTables(PivName).PivotFields(PivField)
        .ClearAllFilters
        .PivotFilters.Add2 Type:=xlCaptionEquals, Value1:=PivValue
    End With
End Sub

Is there anyway I can put the "PROCDATE" in the "Filter" section in the PivotTable instead of "Rows"?

When you record a macro. You will get something like this

ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = "2014"

So simply change the above code to

With ws.PivotTables(PivName).PivotFields(PivField)
    .ClearAllFilters
    .CurrentPage = PivValue
End With

这篇关于有没有办法取消选中数据透视表中除特定项目之外的其他项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！