Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

Two Pivot Tables, one dependent on the other

I have two tabs with a pivot table each in an MS Excel 2007 spreadsheet from the same data source.   Specifically, I have date (MMMYYYY) filter at the top of the 1st tab and another Date filter on the second tab.

How do I connect the two.  I.e., if I select a date in the 1st tab, say AUG2014, how can I relay that select to the pivot table in the second tab?

Thanks,
0
dragonhearts
Asked:
dragonhearts
1 Solution
 
helpfinderIT ConsultantCommented:
In 2007 you can use only VBA (macro) for this. If you can use Office 2010 or 2013 there is a feature called Slicers which is somethig you can use for this
0
 
dragonheartsAuthor Commented:
Sorry, for now, IT has only allowed us to use 2007. Given such, what is the VB macro I can use?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this to see if this works for you.
Don't forget to change the Sheet Names and PivotTable Names as per your actual sheet names and PivotTable Names before running the code.
Sub SetSameFilterInPivotTables()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim myFilter As String

Set ws1 = Sheets("Pivot1")          'Pivot1 is the Sheet which has the PivotTable1

Set ws2 = Sheets("Pivot2")          'Pivot2 is the Sheet which has the PivotTable2

myFilter = ws1.PivotTables("PivotTable1").PivotFields("Date").CurrentPage.Value         'Date is the field name which is dropped in PivotTable Filter Area and myFilter is the filter set in PivotTable1

ws2.PivotTables("PivotTable2").PivotFields("Date").ClearAllFilters          'Clears all the existing Filters from PivotTable2

ws2.PivotTables("PivotTable2").PivotFields("Date").CurrentPage = myFilter

End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now