Excel-VBA：仅更改一个数据透视表的过滤器

i'm searching for a soluton for this:

我正在为此寻找解决方案：

I have a wookbook, in which are multiple sheets, in which are multiple pivot-tables. I simply want to change the Filter of only one specific pivot-table.

我有一个wookbook，其中有多个工作表，其中有多个数据透视表。我只想更改一个特定数据透视表的过滤器。

If I use the ActiveSheet-Method this seems to work but this Sheet is not active when I start this code, so I try to change this filter by adressing it:

如果我使用ActiveSheet-Method这似乎有效，但是当我启动此代码时，此工作表未处于活动状态，因此我尝试通过对其进行更改来更改此过滤器：

Sub Jahresübergang2()

Dim Jahresuebergang2 As Worksheet
    Set Jahresuebergang2 = Worksheets(11)

Jahresuebergang2.PivotTables("Name of Pivot Table").PivotFields("[Incident resolved].[Period].[Year]").VisibleItemsList = Array("[Incident resolved].[Period].[Year].&[2017]")

End Sub

I get Runtime-Error 1004: Run-time error '1004': Method 'PivotTables' of object '_worksheet' failed

我得到运行时错误1004：运行时错误'1004'：对象'_worksheet'的方法'数据透视表'失败

It's like I am doint the wrong object-syntax to change filters of only one table.

就像我使用错误的对象语法来改变只有一个表的过滤器一样。

Here is what the makro recorder is doing:

这是makro记录器正在做的事情：

Sub Makro1()
'
' Makro1 Makro
'

'
    ActiveSheet.PivotTables("Name of Pivot Table").PivotFields( _
        "[Incident resolved].[Year].[Year]").ClearAllFilters
    ActiveSheet.PivotTables("Name of Pivot Table").PivotFields( _
        "[Incident resolved].[Year].[Year]").CurrentPageName = _
        "[Incident resolved].[Year].&[2017]"
End Sub

You see: I don't want to use the activesheet method, due this sheet isn't active when I want to perform this makro.

你看：我不想使用activesheet方法，因为当我想执行这个makro时，这个工作表不活动。

Thanks a lot for your help!

非常感谢你的帮助！

PS: I use Excel 365

PS：我使用的是Excel 365

2 个解决方案

#1

Your code has [Incident resolved].[Period].[Year] whereas the macro recorder has [Incident resolved].[Year].[Year]

您的代码已[事件已解决]。[期间]。[年]而宏录制器已[事件已解决]。[年]。[年]

Change your code to say [Incident resolved].[Year].[Year] and try again.

更改您的代码，说[事件已解决]。[年]。[年]，然后重试。

#2

Here my Solution:

我的解决方案：

TabelleX.Activate (X for the Number of the Sheet)

TabelleX.Activate（X表示工作表编号）

to activate the sheet. The only problem may be that this sheet will be active on the monitor during the makro-process. I solved this with:

激活工作表。唯一的问题可能是在makro过程中该表将在监视器上处于活动状态。我解决了这个问题：

Application.ScreenUpdating = False (Code) Application.ScreenUpdating = True

Application.ScreenUpdating = False（Code）Application.ScreenUpdating = True

so activation won't be updated on the screen.

所以激活不会在屏幕上更新。

#1