Linking two pivot tables

QuestionEdit

You have helped me before (and I was v grateful) I have two small pivot tables on one page.

When I select the "page" field on one I would like it to also update the other thus avoiding relying on individuals to select the same page in both tables. I have seen some code which I can't make work as I dont know enough about VB. as follows:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

????Application.EnableEvents = False

????Dim pt As PivotTable

????For Each pt In ActiveSheet.PivotTables ????????pt.PivotFields("month").CurrentPage = Target.PivotFields("month").CurrentPage.Name ????Next pt

????Application.EnableEvents = True

End Sub

If this is good code I don't know how to create the module as each time I try I cannot select it from my list of macros in "Personal.XLS" as I cant see it

can you help please?

ralph

AnswerEdit

Ralph,

If a routine is marked as Private or it is in a Module marked as private, the code can not be seen in the list of macros to run. You code is marked as private because it is event code. It is designed to be fired bybase on actions taken by the user rather than selected in the dropdown and run like you would do with a normal macro.

This event is pretty new (I think introduced in Excel 2003) and I haven't had a need to play with it, but a cursory look at help would indicate to me that it needs to be instantiated like the refresh events associated with a query table. If you want to put it in personal.xls, then it would have to be instantiated there and work as an application level event. Usually I would see this type of macro being in the workbook where the pivot table is located rather than personal.xls.

If you want to send me the workbook with the pivot table and what version of Excel, I can take a look and try to get a solution for you.