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

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

"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("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. 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("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
>
> "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("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
> >

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" 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("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
> >
> > "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("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
> > >

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" 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("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" 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("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
> > >
> > > "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("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
> > > >

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

"pkern" wrote:

> 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" 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("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" 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("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
> > > >
> > > > "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("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
> > > > >

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

> 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" 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("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" 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("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
> > > >
> > > > "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("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
> > > > >

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

> 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" 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("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" 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("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
> > > >
> > > > "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("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
> > > > >

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

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

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

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