I'm trying to use one pivot table filter to activate both pivot tables on one
sheet in Excel 2007. Code doesn't seem to be working. Any help would be
greatly appreciated.


Private Sub Worksheet_Calculated()


Dim MvPivotPageValue As Variant

Dim Pt As PivotTable 'main pivot table
Dim Pt1 As PivotTable ' 2nd pivot table
Dim WsOther As Worksheet ' name of worksheet
Dim strField As String ' report filter name

Set WsOther = Sheets("Division Summary1")
Set Pt = PVT1
Set Pt1 = WS.Other.PVT2
Set strField = "Fiscal Calendar"

If LCase(Pt.PivotFields("Fiscal Calendar").CurrentPage) <
LCase(MvPivotPageValue) Then
Application.EnableEvents = False
Pt.RefreshTable
MvPivotPageValue = Pt.PivotFields(strField).CurrentPage
Pt1.PageFields(strField).CurrentPage = MvPivotValue
Application.EnableEvents = True
End If

End Sub

Hi,

Whenever you are requesting help, and your code isn't working, it is best if
you state why your code is not working (I know that might sound stupid, but
I'll explain :) ). Sometimes your code may be doing something other than what
you expect, in which case try to explain what you expect and what actually
happens. Sometimes nothing may happen at all, but this isn't likely. Finally,
you may be getting errors, which cause the Debug dialogue.

Having looked at your code I can see some potential problems, but these
might be typos from you putting your code here, or they might be the problem.
Anyway, I'll say what I see and you can see if this helps.

Firstly, you are setting the first pivottable, Pt using

Set Pt = PVT1

I assume PVT1 is the name of the pivottable. Unless PVT1 is an object
variable within your code that already refers to a pivottable, you will
probably get an error here.

To set a pivottable variable, use (and using your sheet variable)

Set Pt = WsOther.Pivottables("PVT1")

When you set Pt1 you have a period in the sheet name. Use above, so:

Set Pt1 = WsOther.Pivottables("PVT2")

Towards the end of your code you use:

Pt1.PageFields(strField).CurrentPage = MvPivotValue

Check your variables carefully, this should be MvPivotPageValue.

At one point you refer to a pivotfield by name ("Fiscal Calendar"), but the
rest of the time you use strField, I would amend this to be consistent,
unless you did this intentionally.

Anyway, these suggestions should hopefully get you a step further. If you
are still having problems then let me know.

Good luck,

Sean.
--
(please remember to click yes if replies you receive are helpful to you)


"pkern" wrote:

I'm trying to use one pivot table filter to activate both pivot tables on one
sheet in Excel 2007. Code doesn't seem to be working. Any help would be
greatly appreciated.


Private Sub Worksheet_Calculated()


Dim MvPivotPageValue As Variant

Dim Pt As PivotTable 'main pivot table
Dim Pt1 As PivotTable ' 2nd pivot table
Dim WsOther As Worksheet ' name of worksheet
Dim strField As String ' report filter name

Set WsOther = Sheets("Division Summary1")
Set Pt = PVT1
Set Pt1 = WS.Other.PVT2
Set strField = "Fiscal Calendar"

If LCase(Pt.PivotFields("Fiscal Calendar").CurrentPage) <
LCase(MvPivotPageValue) Then
Application.EnableEvents = False
Pt.RefreshTable
MvPivotPageValue = Pt.PivotFields(strField).CurrentPage
Pt1.PageFields(strField).CurrentPage = MvPivotValue
Application.EnableEvents = True
End If

End Sub