pivot table filter

  • Thread starter Thread starter samuel
  • Start date Start date
S

samuel

I have two independent pivot tables. Each has 3 filters.

I would like to change the filters of the second pivot table based on the
values in the first pivot table's filters.

Is this possible?
 
hi, samuel !
I have two independent pivot tables. Each has 3 filters.
I would like to change the filters of the second pivot table based on the values in the first pivot table's filters.
Is this possible?
Click to expand...

I can't "see" your layout, but try using vba/code in the '_change' event (in your worksheet/s or workbook object)
to make "other" filter/s match the (current) "changing-cell-filter" ;)

hth,
hector.
 
I recorded a macro and simulated what I want to happen automatically.

Obviously PivotTable5 is what I want to change automatically based on
PivotTable 4

ActiveSheet.PivotTables("PivotTable4").PivotFields("Facility").ClearAllFilters

ActiveSheet.PivotTables("PivotTable4").PivotFields("Facility").CurrentPage = _
"MCMC"

ActiveSheet.PivotTables("PivotTable5").PivotFields("Facility").ClearAllFilters

ActiveSheet.PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = _
"MCMC"
 
hi, samuel !
I recorded a macro and simulated what I want to happen automatically.
Obviously PivotTable5 is what I want to change automatically based on PivotTable 4

ActiveSheet.PivotTables("PivotTable4").PivotFields("Facility").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Facility").CurrentPage = "MCMC"
ActiveSheet.PivotTables("PivotTable5").PivotFields("Facility").ClearAllFilters
ActiveSheet.PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = "MCMC"
Click to expand...

try with your worksheet events code module and something like the following:

Private Sub Worksheet_Calculate()
Me.PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = _
CStr(Me.PivotTables("PivotTable4").PivotFields("Facility").CurrentPage.Value)
End Sub

hth,
hector.

__ OP __
 
hi, samuel !
Sorry, I'm new to VBA, and I'm not following your suggestion.
Click to expand...

- right-click on your worksheet tab/name (where your PT resides) and choose "view code"

- copy/paste the proposed code into "that" code module window:
(if necessary, replace whatever lines on it, assuming you are not using any other code)

Private Sub Worksheet_Calculate()
Me.PivotTables("PivotTable5").PivotFields("Facility").CurrentPage = _
CStr(Me.PivotTables("PivotTable4").PivotFields("Facility").CurrentPage.Value)
End Sub

give it a try by changing your PT4 field ("Facility") and see what happens on your PT5

hth,
hector.
 
Hector,

The code seems to be working correctly. I also have a graph that is based
off of the two tables.

Whenever I change the Month the columns will change in both tables, but the
graph will flicker on and off until Excel closes.

any ideas?
 
Back
Top