Change a pivot field based on another pivot field

[Basil]

Hi,

I have 2 pivot tables on 1 sheet - one shows data broken down monthly, the
other exactly the same data but broken down weekly. Each have two report
filters - one for sport, the other for market.

I want to write some code so that if the filter is changed for the monthly
pivot table, then it will automatically change it in the weekly pivot table.
I've done it before (7 yrs ago!) but lost my work, and now all I get is
errors.

Here is the latest version of code that I've tried (must have tried 20
different variations of code):

If Range("Piv_Sport") <> Range("Piv_Sport_Check") Then
ActiveSheet.PivotTables("Weekly").PivotFields("Sport").CurrentPage =
Range("Piv_Sport")
Range("Piv_Sport_Check") = Range("Piv_Sport")
Else
ActiveSheet.PivotTables("Weekly").PivotFields("Market
Search").CurrentPage = Range("Piv_Market")
Range("Piv_Market_Check") = Range("Piv_Market")
End If

Piv_Sport and Piv_Market are named ranges referring to the cells of the
report filters on the monthly (master) pivot table. The _check cells are
adjacent cells that I use to identify if the report filter has been changed
in the worksheet_change code

The most common error I get for my code variations (including for the above)
is runtime 1004: unable to get the pivotfields property of the pivottable
class.

The pivot tables have exactly the same data and structure, except that one
has month in row labels, and the other week in row lables. Excel 2007 as u
might have guessed.

Thanks for any help!

Basil

 

[Basil]

I did it eventally. It seems that Excel 2003 onwards can have issues with
this. My code was correct in the first place but I had to rebuild everything.
Also although I would assume that I could reference the current page of one
pivot table to set the current page of the other pivot table, Excel coud not
get it, so I had to reference a cell that pointed at the current page of the
pivot table:

If Range("Piv_Sport") <> Range("Piv_Sport_Check") Then
Range("Piv_Sport_Check") = Range("Piv_Sport")
ActiveSheet.PivotTables("Weekly").PivotFields("Sport").CurrentPage =
Range("Piv_Sport").Value
Else
Range("Piv_Market_Check") = Range("Piv_Market")
ActiveSheet.PivotTables("Weekly").PivotFields("Market
Search").CurrentPage = Range("Piv_Market").Value
End If

Basil

 

[pkern]

Basil. this is exactly what i need and since I'm new to excel could you
please provide me with the worksheet_change code you referenced to check to
see if the filter changed. Any help you could provide would be greatly
appreciated.

 

[Basil]

Hi pkern,

The code I wrote was actually everything in worksheet_change. I'll talk you
through it so that you can apply something similar for your work - u might
need to read it twice:

Private Sub Worksheet_Change(ByVal Target As Range)

'Stage 1: check to see if the sport or market pivot fields have been changed
by the user, and exit if not.

If Range("Piv_Sport") <> Range("Piv_Sport_Check") Or Range("Piv_Market")
<> Range("Piv_Market_Check") Then
Application.ScreenUpdating = False
Else
Exit Sub
End If
' I have named the cell within the pivot table that holds the filter for the
Sport field as 'Piv_Sport', and the cell to the right of this
'Piv_Sport_Check'. Same with Market. At starting point they will be the same,
so if this is not the case then the field must have been changed and I have
disabled screen updating in preparation for work in stage 2. If they are
still the same, then there has been no change to this (either) field, hence
exit sub.

'Stage 2: Identify which field has changed, change 2nd pivot table to match
and reset checks to start point

If Range("Piv_Sport") <> Range("Piv_Sport_Check") Then
Range("Piv_Sport_Check") = Range("Piv_Sport")
ActiveSheet.PivotTables("Weekly").PivotFields("Sport").CurrentPage =
Range("Piv_Sport").Value
'If it was Sport that was changed, then the pivot field cell and the cell to
its right will no longer be equal, hence this if will return true. The next
line sets the cell to the right of the pivot field to be equal to the pivot
field - this effectively 'resets' the checking process to the start point.
The third line then sets the field in the 2nd pivot table to match.

Else
Range("Piv_Market_Check") = Range("Piv_Market")
ActiveSheet.PivotTables("Weekly").PivotFields("Market
Search").CurrentPage = Range("Piv_Market").Value
End If
'This does the same process in the case that the user changed the Market field

Application.ScreenUpdating = True

End Sub
'These last two lines just re-activate screen updating and end the sub.


Additionally I have another bit of code on this sheet that will
automatically refresh all pivot tables when the sheet is activated - you
might find it useful:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True
End Sub

Hope that helps.

B

 

[pkern]

OK so my main table is called PVT1
Fiscal_Cal is the field my report filter is in
Fiscal_Calendar_Check is the field beside for checking
PVT2 is the second table
PVT2Date is the date the report filter is in

Here is my code

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("Fiscal_Cal") <> Range("Fiscal_Calendar_Check") Then
Application.ScreenUpdating = False
Else

Exit Sub

End If


If Range("Fiscal_Cal") <> Range("Fiscal_Calendar_Check") Then
Range("Fiscal_Calendar_Check") = Range("Fiscal_Cal")
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?

thanks

 

[Basil]

Hi pkern,

Sorry for the delay - was away for the weekend (and didn't get a notify
thing!)

It seems ok (assuming the filters are the same format in both pivots - eg
date format); what is the error you're getting?

If you only have the one field to consider, you can simplify the code as
follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("Fiscal_Cal") <> Range("Fiscal_Calendar_Check") Then
Application.ScreenUpdating = False
Range("Fiscal_Calendar_Check") = Range("Fiscal_Cal")
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage
= Range("Fiscal_Cal").Value
Application.ScreenUpdating = True
End If

End Sub

Like I said, recent versions of Excel aren't reliable in creating such code
(errors for no reason!) - believe me it's true. So you could try replacing
the action line with:
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage
= ActiveSheet.PivotTables("PVT1").PivotFields("PVT1Date").CurrentPage

Or also try rebuilding the pivot tables

Have a go at these and let me know how you get on...

B

 

[Basil]

Darn thing - crashed on me after I wrote your reply!

Sorry for the delay, was away for weekend (and didn't get a notify - darned
thing!)

Assuming the filters are the same format in the two pivots, it seems ok.
What error are you getting?

It can be simplified a fair bit if you only have one filter to consider:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("Fiscal_Cal") <> Range("Fiscal_Calendar_Check") Then
Application.ScreenUpdating = False
Range("Fiscal_Calendar_Check") = Range("Fiscal_Cal")
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage
= Range("Fiscal_Cal").Value
Application.ScreenUpdating = True
End If

End Sub

Like I said earlier, later versions of Excel have proven themselves
unreliable at times when creating code on pivot tables (errors for no reason
- believe me!). You can try replacing the code on the pivot action with:
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage =
ActiveSheet.PivotTables("PVT1").PivotFields("PVT1Date").CurrentPage

or try rebuilding the pivot tables (worked for me twice!)

So check:
- formats of filter fields
- condensed code above
- replacing pivot action code
- rebuilding pivot tables

and let me know what the error code was. Will take it from there if still no
joy.

B

 

[Basil]

Darn thing - crashed on me after I wrote your reply (twice)!

Sorry for the delay, was away for weekend (and didn't get a notify - darned
thing!)

Assuming the filters are the same format in the two pivots, it seems ok.
What error are you getting?

It can be simplified a fair bit if you only have one filter to consider:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("Fiscal_Cal") <> Range("Fiscal_Calendar_Check") Then
Application.ScreenUpdating = False
Range("Fiscal_Calendar_Check") = Range("Fiscal_Cal")
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage
= Range("Fiscal_Cal").Value
Application.ScreenUpdating = True
End If

End Sub

Like I said earlier, later versions of Excel have proven themselves
unreliable at times when creating code on pivot tables (errors for no reason
- believe me!). You can try replacing the code on the pivot action with:
ActiveSheet.PivotTables("PVT2").PivotFields("PVT2Date").CurrentPage =
ActiveSheet.PivotTables("PVT1").PivotFields("PVT1Date").CurrentPage

or try rebuilding the pivot tables (worked for me twice!)

So check:
- formats of filter fields
- condensed code above
- replacing pivot action code
- rebuilding pivot tables

and let me know what the error code was. Will take it from there if still no
joy.

B

 

[pkern]

Hi Basil. Thank you so much for getting back with me. The error i get is:

Runtime Error 1004:
Unable to get the PivotFields property of the PivotTable Class

Thanks agin
Paula

 

[Basil]

Did you try the things I suggested?

If they didn't work u could just send it to me if u want (I dunno what
protocol is on this site)?

B

 

[pkern]

Basil. I also tried this code. But no luck

Private Sub Worksheet_Calculated()


Dim MvPivotPageValue As Variant

Dim Pt As PivotTable
Dim Pt1 As PivotTable
Dim WsOther As Worksheet
Dim strField As String

Set WsOther = Sheets("Division Summary")
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