如何使多个数据透视表在excel中的模板中模仿另一个透视表的过滤器（报告和行标签）？ [英] How can I make multiple pivot tables mimic the filters (report and row label) of another on just that sheet in excel?
问题描述
我正在寻找（我猜测一个宏是唯一的办法） ，是一种能够更改该工作表中枢轴之一的过滤器的方法，并且仅在该工作表上更新其他枢轴，以模拟我所更改的过滤器（报告和行标签）。在其他的数据透视表中，没有其他的数据可能会改变 - 只是过滤器。
不幸的是，我完全不了解vba编码（我知道一些java和东西，我从来没有做任何宏编码）。我能够从互联网复制一个宏，它是我所需要的一部分;它会更新报告过滤器，但不更新行标签过滤器中的日期。这里是代码：
Option Explicit
Private Sub Worksheet_PivotTableUpdate（ByVal Target As PivotTable）
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
设置wsMain = ActiveSheet
设置ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
'更改活动工作表上所有枢轴表的所有字段
对于每个pfMain在ptMain。 PageFields
bMI = pfMain.EnableMultiplePageItems
对于每个pt在wsMain.PivotTables
如果pt < ptMain然后
pt.ManualUpdate = True
设置pf = pt.PivotFields（pfMain.Name）
bMI = pfMain.EnableMultiplePageItems
与pf
.ClearAllFilters
选择案例bMI
案例False
.CurrentPage = pfMain.CurrentPage.Value
案例True
.CurrentPage =（全部）
对于每个pi在pfMain。 PivotItems
.PivotItems（pi.Name）.Visible = pi.Visible
下一个pi
.EnableMultiplePageItems = bMI
结束选择
结束
bMI = False
设置pf =不铰链
pt.ManualUpdate = False
结束如果
下一个pt
下一个pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
有没有办法做我想要的做？你的帮助将是非常感激的！
没有看到你的枢纽，似乎你需要做的所有代码重复设置透视字段值以反映日期的步骤。这是通过
添加由行设置的循环的变体：
set pf = pt.pivotfields （pfmain.name）
至
set pf = pt.pivotfields（pfmain.nameofyourrowfiltervariable）
完整代码在下面，我也会避免错误恢复下一步
Option Explicit
Private Sub Worksheet_PivotTableUpdate（ByVal Target As数据透视表）
错误恢复下一步
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
设置wsMain = ActiveSheet
设置ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
'更改所有字段活动工作表上的枢轴表
对于每个pfMain在ptMain.PageFields
bMI = pfMain.enablemultiplepageitems
对于每个pt在wsMain.PivotTables
如果pt < ptMain然后
pt.ManualUpdate = True
设置pf = pt.PivotFields（pfMain.Name）
bMI = pfMain.EnableMultiplePageItems
与pf
.ClearAllFilters
选择案例bMI
案例False
.CurrentPage = pfMain.CurrentPage.Value
案例True
.CurrentPage =（全部）
对于每个pi在pfMain。 PivotItems
.PivotItems（pi.Name）.Visible = pi.Visible
下一个pi
.EnableMultiplePageItems = bMI
结束选择
结束
bMI = False
设置pf = pt.pivotfields（pfMain.nameofdatevariable）
dates = pf Main.enablemultiplepageitems
with pf
.clearallfilters
选择案例日期
案例false
.currentpage = pfmain.currentpage.value
案例true
pppmain.pivotitems中的每个pi的
.pivotitems（pi.nameofdatevariable）.visible = pi.visible
next pi
.enablemultiplepageitems =日期
end select
end with
date = false
set pf = nothing
pt.ManualUpdate = False
End If
Next pt
下一个pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
尝试它，看看它是否有效我还没有测试这个
So I have multiple pivot tables on a single sheet in excel. I have a long list of months in the row labels. The report filter is filterable by names. All of the columns in the pivots are just sums of data.
What I'm looking for (and I'm guessing a macro is the only way to do it), is a way to be able to change the filters of one of the pivots on that sheet and it update the other pivots on only that sheet to mimic the filters (both report and row label) of the one that I changed. Nothing else should change in the other pivot tables - just the filters.
Unfortunately, I literally know nothing about coding vba (I know a bit of java and stuff but I've never done any macro coding). I was able to copy a macro from the internet that did part of what I need; it updates the report filter but doesn't update the dates in the row label filter. Here's the coding for that:
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
'change all fields for all pivot tables on active sheet
For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
For Each pt In wsMain.PivotTables
If pt <> ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False
Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Is there anyway to do what I'm looking to do? Your help would be GREATLY appreciated!
Without seeing your pivottables it seems that all you need to do to the above code is repeat the steps from setting the pivotfield value to reflect the date. This is done by adding a variant of the loop set up by the line:
set pf = pt.pivotfields(pfmain.name)
to
set pf = pt.pivotfields(pfmain.nameofyourrowfiltervariable)
the full code is below, i would also avoid on error resume next
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
'change all fields for all pivot tables on active sheet
For Each pfMain In ptMain.PageFields
bMI = pfMain.enablemultiplepageitems
For Each pt In wsMain.PivotTables
If pt <> ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False
Set pf = pt.pivotfields(pfMain.nameofdatevariable)
dates = pfMain.enablemultiplepageitems
with pf
.clearallfilters
select case dates
case false
.currentpage = pfmain.currentpage.value
case true
.currentpage = "(All)"
for each pi in pfmain.pivotitems
.pivotitems(pi.nameofdatevariable).visible = pi.visible
next pi
.enablemultiplepageitems = dates
end select
end with
date = false
set pf = nothing
pt.ManualUpdate = False
End If
Next pt
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
try it and see if it works I have not tested this
这篇关于如何使多个数据透视表在excel中的模板中模仿另一个透视表的过滤器（报告和行标签）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
