VBA or other method to connect multiple pivot tables w/ different data sets to same slicer?

[kelle]

I'm working on a project that will provide an overview of all of our data for the last 10 years. I have five pivot tables, all filterable by our city wards. Three are based on the same data set I've connected those three to the same slicer.

However, the two remaining pivot tables work off two different data sets. These cross-reference the ward a person lives in against a ward they work in. Both include the same ward field twice, one named wardresidence in the data table, the other named wardworkplace in the data table. In the two pivot tables I've renamed the fields to "Ward" to match the report filter of the first three pivot tables, but because they're on different data sets, I can't link the slicers, which I'd really like to do. Is there any way with VBA or any other method to make this work?

 

[mart37]

You can link two datasets in PowerPivot.

 

[kelle]

You can link two datasets in PowerPivot.

Thanks so much, mart--I managed to get IT to install PowerPivot for me. Would you have a resource to walk me through how to link the two with PowerPivot?

 

[mart37]

https://www.myexcelonline.com/blog/linking-excel-tables-power-pivot/

 

[kelle]

-So PowerPivot doesn't seem to want to work for me. Is there any VBA way to accomplish this? I tried the script in this post https://www.mrexcel.com/forum/excel...lter-vba-choose-one-other-2-change-match.html but it seems to return an error, as every time I try to select the option it goes through the process but then just defaults to no slicer selection.

To be clear, I have 5 pivot tables on one sheet. The first three are based on the same dataset. I have three slicers based on three different metrics, which control all three pivot tables. The filters are:

- Neighbourhood
- Community Area
- Ward

I then have two other pivot tables, based on two different data sets. They both have corresponding fields for Neighbourhood, Community Area, and Ward, which matching results. I'd like to have the last two pivot tables filter on the same content based on the slicer selections.

 

[mart37]

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Application.EnableEvents = False
Set pt = ActiveSheet.PivotTables("Pivottable4")
Set pf = pt.PivotFields("Ward")
With pf
    .ClearAllFilters
    .EnableMultiplePageItems = True
    For Each pi In .PivotItems
        pi.Visible = ActiveWorkbook.SlicerCaches("Slicer_Ward").SlicerItems(pi.Name).Selected
    Next pi
End With
Application.EnableEvents = True
End Sub

This is an event-macro.
Place it on the sheet with the pivottables.

 

[kelle]

Thanks, mart, but no such luck--I entered the code in the Pivot Tables sheet, changed the "Pivottable4" to the matching pivot table name, but nothing's happening.

 

[Greg Truby]

-So PowerPivot doesn't seem to want to work for me.

I apologize if I missed it, but have you indicated which version of Excel you're using? If 2016 or even 2013, you ought to be able to get PowerPivot to work and really, that would be by far the easiest way to pull this off.

If you are using 2016, then please clarify what you meant by "...doesn't seem to work for me.".

 

[kelle]

I apologize if I missed it, but have you indicated which version of Excel you're using? If 2016 or even 2013, you ought to be able to get PowerPivot to work and really, that would be by far the easiest way to pull this off.

If you are using 2016, then please clarify what you meant by "...doesn't seem to work for me.".

Hi Greg--I'm using Excel 2010 (work computer). Every time I try to use Powerpivot, it freezes up (possibly because some of the datasets I'm using are near 800,000 rows?).

 

[Greg Truby]

Hi Greg--I'm using Excel 2010 (work computer). Every time I try to use Powerpivot, it freezes up (possibly because some of the datasets I'm using are near 800,000 rows?).

Yuck. PowerPivot plays a lot nicer with newer versions of Excel. If it's freezing up on you and upgrading all of the consumers of this workbook to Excel 2016 is not in the cards then code like mart37 is what you'll need to use. First thing I would do is to go into Excel and use the contextual pivottable tools | analyze tab in the ribbon to rename my pivots to something more meaningful than "pivottable4".


Then edit mart's code to something like:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Application.EnableEvents = False
Set pt = ActiveSheet.PivotTables("myveryeffectivelynamedpivottable")
Set pf = pt.PivotFields("Ward")
With pf
    .ClearAllFilters
    .EnableMultiplePageItems = True
    For Each pi In .PivotItems
	debug.print pi.name, ActiveWorkbook.SlicerCaches("Slicer_Ward").SlicerItems(pi.Name).Selected
        pi.Visible = ActiveWorkbook.SlicerCaches("Slicer_Ward").SlicerItems(pi.Name).Selected
    Next pi
End With
Application.EnableEvents = True
End Sub

And step the code watching the immediate window to see what's going on. Use debug.prints & the immediate window or set watches and step code and keep playing and learning.