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