How to connect 1 Slicer to 2 different PivotTables - Excel 2010

[Legacy 227120]

I am working with PivotTables and Slicers. However if I can first explain the scenario:

• I am collating data from a table that is updated every month. I transfer the table into Excel 2010. The column titles on the monthly tables are always the same, it is the data that changes.
• For example, I have the xls spreadsheets from August 2012 and September 2012. From these 2 tables I create a PivotTable for both months, structuring them exactly the same.
• I then Place both PivotTables side by side on the same page.
• I then inserted a Slicer for table A.

I would like to know if I can get the Slicer to control BOTH PivotTables at the same time, bearing in mind that both PT's are drawing their data from different sources.
Please help!

 

[Legacy 227120]

Hi Hartke2421

The Macro code is listed below, where DNSP, PSNSP, GCNSP are the 3 fields in each slicer. And True/False refers to which fields need to be displayed at the same time. I hope this helps. If not, let me know and I will run you through how I recorded the macro.

[B]Sub DNSP()[/B]
[B]'[/B]
[B]' DNSP Macro[/B]
[B]'[/B]

[B]'[/B]
[B]    With ActiveWorkbook.SlicerCaches("Slicer_Type")[/B]
[B]        .SlicerItems("DNSP").Selected = True[/B]
[B]        .SlicerItems("GCNSP").Selected = False[/B]
[B]        .SlicerItems("PSNSP").Selected = False[/B]
[B]    End With[/B]
[B]    With ActiveWorkbook.SlicerCaches("Slicer_Type1")[/B]
[B]        .SlicerItems("DNSP").Selected = True[/B]
[B]        .SlicerItems("GCNSP").Selected = False[/B]
[B]        .SlicerItems("PSNSP").Selected = False[/B]
[B]    End With[/B]
[B]End Sub[/B]

 

[Hartke2421]

Thanks, I'll try to work with this, but I think I need the macro to be a little more dynamic. I want the second slicer to mimic whatever is selected in the first slicer.

 

[Jerry Sullivan]

Hi Hartke2421, Are you using PowerPivot or just Excel PivotTables?

There are many code examples on this forum that show how to sync the manual filters on PivotTables so that when a Master is changed, all the other Pivots with the same field have the same filters applied.

That code could be modified to do a similar process with slicers instead of manual filters.

 

[Legacy 227120]

Thanks, I'll try to work with this, but I think I need the macro to be a little more dynamic. I want the second slicer to mimic whatever is selected in the first slicer.

No worries, this macro does exactly what you require (as I needed the same thing). To be honest, in this case it would be easier simply recording the macro as opposed to coding it. They are only simple steps that require no code work. Good luck!

 

[Hartke2421]

JS411, I'm just using Excel's pivot tables, but I wasn't sure if PowerPivot could do this more easily. I've been looking for macros like this for a while and can't find anything. Can you direct me to one?

FShaikh, are you suggesting to record separate macros to change to each selected pivot item in the field? If that's the case, I'm not sure that's a good option for me because there are 36 different pivot items in this field.

 

[NickyvV]

As I read your question I assume you are using regular PivotTables? Have you tried PowerPivot (Tables) then?
By importing the data as a Linked Table in PowerPivot you can relate the two tables. By making a relationship between the tables you can link 1 slicer to many PT's. You could even make measures that compare the current data with that from last month.
Please let me know if this was helpful!

 

[Hartke2421]

NickyvV,
The more research I do, the more I think that PowerPivot might be my best option. Thanks for the suggestion and I think I'll try that in the meantime while I continue to look for ways for this to work with regular Pivot tables in Excel.

Any links on where to do this in PowerPivot?

 

[NickyvV]

Well,
Rob Collie's blog (New Visitor? « PowerPivotPro) would be a good one!
I don't have a specific example at the ready, let me know if this works for you..

 

[canpa12]

Thanks again MD610. I did not know that about SharePoint, however I wont be publishing this for the time being. Helpful info tho!

Dear Fshaik kindly share the macro that you recorded.. i am also having same kind of issue

 

[canpa12]

No worries, this macro does exactly what you require (as I needed the same thing). To be honest, in this case it would be easier simply recording the macro as opposed to coding it. They are only simple steps that require no code work. Good luck!

Dear Fshaik Can you guide me how to use macros to control slicers ( am new to macros recording) am giving my email id..

E-Mail Address Removed - Moderator