Macro for a Pivot Table selection

[nilsan7]

Hi there, I have a simple question. On a worksheet, I have 2 Pivot Tables looking at the same data. I would like the user to select an item in Table1 and the Table2 should also use that same option. How do I write a macro for that? So what I mean is, let us say the user has to select a city and there are 10 cities, s/he chooses AUCKLAND in the PivotTable1 under CITY and the same should be selected automatically in PivotTable2 under CITY. I have 2 charts linked to these 2 Tables which will update automatically so all I want is the user to select it once. Can you please help me? Thanks

 

[Tom Urtis]

What's the purpose of having 2 separate pivot tables if one should show what the other should show? Can you not have 2 charts off just one table?

 

[nilsan7]

Hello, the data has 2 sets of numbers that I want to extract and hence I have 2 Pivots. For eg: AUCKLAND may have the population listed in numbers but daily cash sales in Dollars and they are 2 data sets and hence I have 2 tables, one for numbers and one for $$. This example by the way is only an example. Hope this clarifies, thank you.

 

[nilsan7]

Let me put it this way, I just want a macro which will run the following code when there is a change in selection within another PivotTable1.

Range("F5").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Area").CurrentPage = Range("b4").Text


Please help - thanks

 

[Marcelo Branco]

Let me put it this way, I just want a macro which will run the following code when there is a change in selection within another PivotTable1.

Range("F5").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Area").CurrentPage = Range("b4").Text


Please help - thanks

Maybe this
(copy to the worksheet code-page)

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    If Target = PivotTables("PivotTable1") Then
        Application.EnableEvents = False
        Range("F5").Select
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Area").CurrentPage = Range("b4").Text
        Application.EnableEvents = True
    End If
End Sub

HTH

M.

 

[nilsan7]

Great - this code works - thanks a lot

If I have to learn more VBA codes etc, which is the best site or book, what would you recommend please? Thanks

 

[Marcelo Branco]

Great - this code works - thanks a lot

If I have to learn more VBA codes etc, which is the best site or book, what would you recommend please? Thanks

You are welcome.

Yes this code works, but if you are trying to do what i'm supposing - to set the same filter in both PageFields in the two PVTs, ie when you select, say Area1, in one PVT page-field the other PVT also gets filtered to show only Area1 - maybe this is a better and more elegant code.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    If Target = PivotTables("PivotTable1") Then
        Application.EnableEvents = False
        PivotTables("PivotTable4").PageFields("Areas").CurrentPage = _
         PivotTables("PivotTable1").PageFields("Areas").CurrentPage.Name
        Application.EnableEvents = True
    End If
End Sub

A very good book:
EXCEL 2010 Power Programming with VBA
John Walkenbach

Ths for providing feedback

M.

 

[nilsan7]

Thanks marcelo - however, the new code does not work? I have changed the Areas to Area so it is not that. Also, not that I am still using Excel 2003 and not 2010. Just so you know, I work in Auckland, NZ. Thanks again for your help and advice on the book. Any website you recommend following as well for tips and views on coding etc?

 

[Marcelo Branco]

Hi Nilsan7,

The code should work, but probably some reference (pv name or something else) is wrong, but it's not so important since my previous code is working.

Take a look at
http://www.amazon.com/Excel-2003-Power-Programming-VBA/dp/0764540726

http://www.cpearson.com/Excel/MainPage.aspx

M.

 

[nilsan7]

I checked again and the new code works now - thanks Marcelo