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("PVT2D ate").CurrentPage
= Range("Fiscal_Cal").Value
End If

Application.ScreenUpdating = True

End Sub

What am i missing?

thanks

"Basil" wrote:

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("Spo rt").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("Mar ket
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" wrote:

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" wrote:

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("Spo rt").CurrentPage =
Range("Piv_Sport").Value
Else
Range("Piv_Market_Check") = Range("Piv_Market")
ActiveSheet.PivotTables("Weekly").PivotFields("Mar ket
Search").CurrentPage = Range("Piv_Market").Value
End If

Basil

"Basil" wrote:

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("Spo rt").CurrentPage =
Range("Piv_Sport")
Range("Piv_Sport_Check") = Range("Piv_Sport")
Else
ActiveSheet.PivotTables("Weekly").PivotFields("Mar ket
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