change pivot table in code

[Basil]

Hi,

Does anybody know some example code I can use for the following situation:

I have 2 pivot tables on a sheet which both have the same report filters. If
I change the filter selection on pivot table 1, I want (through code) for
pivot table 2 to automatically apply the same filter selection.

Hope u can help,

Thanks,

Basil

 

[A&A - In Any Place]

See this solution in: http://inanyplace.blogspot.com/



ANDRÉ BERNARDES
A&A - In Any Place

Acesse o portfólio:
http://www.slideshare.net/bernardes/informaes-ao-invs-de-dados-presentation

MSN: (e-mail address removed)
SKYPE: inanyplace
TWITTER: bernardess

My Profile: http://al-bernardes.sites.uol.com.br/
My Blog: http://inanyplace.blogspot.com/
In LinkedIn: http://www.linkedin.com/in/andrebernardes
In Plaxo: http://andrebernardes.myplaxo.com/
In Jobster: http://www.jobster.com/people/andrebernardes
In Unyk: http://www.unyk.com/pt/id/9571234

My experiences:
http://andrelbernardes.sites.uol.com.br/MIS.html
http://andrelbernardes.sites.uol.com.br/XLS.html

Acesse meus Feeds:
http://www.google.com/reader/shared/17429357110218427555

 

[Basil]

Hi Andre,

Thanks for your response. I couldn't find the solution there (although my
spanish isn't perfect). I only saw the blog on how to refresh all pivot
tables (which I actually already do in a different way by using
activeworkbook.refreshall).

I need code to change the filter on pivot table 2 when a user changes the
filter on pivot table 1.

Thanks,

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