用于运行数据透视表和更改过滤器/标签的VBA代码

Effectively I am trying to create some VBA code that will loop through a bunch of sheets with pivot tables, apply certain conditions to the pivot tables (i.e. change the fields depending on a variable/change the filter depending on a cell value, as I have tried to demonstrate in the code below). This doesn't even come close to running (I am a complete beginner trying to update legacy code), so I am looking for pointers as to how to use the variables defined for the results I want.

实际上,我正在尝试创建一些VBA代码,它将遍历带有数据透视表的一堆工作表,将某些条件应用于数据透视表(即根据变量更改字段/根据单元格值更改过滤器,因为我有试图在下面的代码中演示)。这甚至没有接近运行(我是一个完整的初学者尝试更新遗留代码),所以我正在寻找如何使用为我想要的结果定义的变量的指针。

My current issue is that I can't seem to call the PivotTable to start changing fields etc.

我目前的问题是我似乎无法调用数据透视表来开始更改字段等。

Option Explicit
Dim MySheet As Worksheet
Dim MyPivot As PivotTable
Dim NewCat As String

Sub RefreshPivots()

 For Each MySheet In ActiveWorkbook.Worksheets
    For Each MyPivot In MySheet.PivotTables
        NewCat = Worksheets("WorkingSheet").Range("B44").Value
        MyPivot.RefreshTable

        With ActiveSheet
         .MyPivot.AddDataField
         .MyPivot.PivotFields ("GuidelinePercent"), "Count of GuidelinePercent", xlCount
        End With

    Next MyPivot
 Next MySheet

(A lot more will go in the loop, this is just the first issue I have encountered).

(循环中会有更多内容,这只是我遇到的第一个问题)。

I am getting: Run-time error '438' Object doesn't support this property or method on line

我得到:运行时错误'438'对象不支持此属性或方法在线

With ActiveSheet.MyPivot.AddDataField

Any help would be great!

任何帮助都会很棒!

EDIT;

When trying

With ActiveSheet
.PivotTables(MyPivot).AddDataField
.PivotTables(MyPivot).PivotFields ("GuidelinePercent"), "Count of GuidelinePercent", xlCount
End With

I get Run-time error'1004': Unable to get the Pivot Tables property of the Worksheet class on the line .MyPivot.AddDataField.

我得到运行时错误'1004':无法在行.MyPivot.AddDataField上获取Worksheet类的数据透视表属性。

1 个解决方案

#1

You have a loop of For Each MySheet In ActiveWorkbook.Worksheets, so when you are using later With ActiveSheet it is not the same sheet as MySheet, infact you don't need to use ActiveSheet, you can use directly the PivotTable object you name MyPivot.

你有一个For Each MySheet循环在ActiveWorkbook.Worksheets,所以当你以后使用ActiveSheet它不是与MySheet相同的表,你不需要使用ActiveSheet,你可以直接使用你命名为MyPivot的数据透视表对象。

Try the code below:

请尝试以下代码:

 With MyPivot
     .AddDataField .PivotFields("GuidelinePercent"), "Count of GuidelinePercent", xlCount
 End With

Edit 1:

 Dim PvtFld As PivotField

 With MyPivot
     On Error Resume Next
     Set PvtFld = .PivotFields("GuidelinePercent")
     On Error GoTo 0
     If PvtFld Is Nothing Then
         MsgBox "Error in setting the PivotField"
     Else
         .AddDataField PvtFld, "Count of " & PvtFld.Name, xlCount
     End If
 End With