Hi Hooi,

You can try this macro to disconnect all slicers:

To use the code, follow the instructions from here: how-to-use-vba-code-from-the-internet

Hi Catalin,

Thanks for your help.Glad to receive your response.

I am newbie on macro, sorry that i am still not understand the code meaning.

Why do we have to disconnect all the slicers? 

For this below part, is it the meaning that i have to put the slicer name and the desired pivot tables name that i want to connect with?

'create a dictionary with slicers and connected pivot tables
For Each sl In ThisWorkbook.SlicerCaches
SlicersDict.Add Key:=sl.Name, Item:=sl.PivotTables
Next

For the situation i am facing is like:

Slicer A connect with table B (not pivot table,connected with ms access)

Slicer A not able to connect with pivot table C (source from table B)

I wanted to connect Slicer A connect with pivot table C. How do i make it?

Hope you can explain for further details.Thanks again!

yhooithin05 said
Why do we have to disconnect all the slicers? 

 Well, that's the name of the subject: "macro to detach all slicers from all pivot tables"

Why would you choose such a name and ask this question? 🙂

A defined table does not have a cache. Only a pivot table has a pivot cache, there is nothing to be shared between those 2 different objects.

All you can do is to write a code that will read the .Selected attribute from all items in a slicer, and apply it to the other slicer.

Check the attached file for an example. I used a code like this one, for pivot table vb sheet module:

On the other sheet, with a defined table, something like this should be used:

it's just an example, the code should be more complex, to take into consideration all possible problems, it might not work perfectly in this basic form.

Catalin

Hi Catalin,

I am sorry for my bad english. I have misunderstand the "detach" meaning. I wanted to ask how to connect slicers one 😀

I've tried it follow like your example. It works like charm! Don't know how to thank you 🙂 But now one of the thing is when i click on the item on slicer,it loads very slow only can filtered out result.I found out the reason might be because of i have too many slicers in the workbook.I have many tabs and slicers so total have more than 20 slicers 🙁

The codes is running through all the slicers so causing the process running slow?  

For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems----> Can i run through only the selected sheet but not whole workbook?And what code for worksheet?

Thanks again!!

 Regards,

Hooi Thin

yhooithin05 said
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems----> Can i run through only the selected sheet but not whole workbook?

The code is not running through the entire workbook, it is looping through all Slicer Items from the SlicerCache named Slicer_Field1, the slicer cache is stored in ThisWorkbook, that's what the line of code you mentioned means.

The code has to check each record from that column, to see if it is visible (selected) or not, and apply the same setting to the mirrored slicer:

SlItem.Selected can be true or false.

If the code takes a long time to run, you may have a large number of unique entries in that column.

You can try to set ScreenUpdating to false at the beginning of the code, and set it back to true at the end, this may increase execution speed.:

Application.ScreenUpdating=False

The defined table code, should disable events before applying the attributes to the pivot table, otherwise the pivot table change event may trigger that code too.

Application.EnableEvents=False (set back to true at the end of code)

Catalin

I've tried to put Application.ScreenUpdating = False and even Application.EnableEvents=False on defined table code, but still not working. The slicers still loading very slow. When i try to debug and point to the code "Application.ScreenUpdating = False" (its showing true)

Is there any other method to speed them up?

Pivot table

Option Explicit

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim SlItem As SlicerItem
Application.ScreenUpdating = False 

For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter").SlicerItems ' this field is from pivot table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next

For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month").SlicerItems ' this field is from pivot table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next

For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion").SlicerItems ' this field is from pivot table slicer
ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next

Application.ScreenUpdating = True 

End Sub

Defined Table 

Option Explicit

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
Dim SlItem As SlicerItem
MsgBox 1
Application.EnableEvents = False

For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter1").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Quarter").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next

For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month1").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_Order_Line_Trans_Calendar_Month").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next

For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion1").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_OU_Geo_Hier_Superregion").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next

Application.EnableEvents = True

End Sub

Regards,

Hooi Thin

I do have to say that I don't really understand the need of filtering both the source table and the pivot table report made from that table.

Maybe if you can explain what is it you want to achieve, we can find a different approach, we don't have to struggle in a possibly wrong design concept. There is an old saying: "all the roads lead to Rome", and another interpretation is saying that: "there is more than 1 road to Rome". In excel, there are always multiple alternatives to obtain the same output.

Take your time and prepare a sample file with as many details as possible, so we can understand your situation. In most cases, there is no need for VBA, we have a lot of powerful tools that we can use.

Cheers,

Catalin

Thanks Catalin for your kind patience and efforts to help me clarify all the things.

Attached my example code hope can make clear situation.My intention is to connect slicer on order details with order charts, previously before using your code, i am not able to control the charts using the slicer on order details. That moment i have to create another slicer on the order charts so both the page is independent to each other.

Now i added the code that you given,currently my slicer on order details can control the defined table and charts as well.
 And i am almost to complete the target i want but now facing speed issue on running the slicer. If it is no need for VBA it will be great, but i am stuck on here right now. 

Thanks again for your assistance!

Regards,

Hooi Thin 

Hi Hooi,

I suggest leaving the source data as is, use that table just for data entry, not for reports.

See the attached file, Sheet1 does exactly the same thing as you did with table slicers, it's a pivot table returning all columns from the database, no aggregation.

And you can connect all slicers to all reports, they have the same source now, and it's much faster.

Catalin

Oh Catalin, bravo! 🙂

You know that i have been tried to put like your way as pivot table previously ,but it showed as compact form by default, i don't even go to figure out actually it have a report design setting which can display as outline form like table forms and i just give it up to replace as a table, end up wasting time on other parts.

Luckily i've meet you if not i don't know how much time i need to waste again and maybe go to holland..XD

Now i know the powerful tools in excel, just a simple setting only..LOL

Anyway, really thanks you very much for all this way to help me and explain to me the details. You are my life saver! God Bless you 😀 😀

Thanks & Regards,

Hooi Thin

Glad to hear that your problem is solved, we are here to help 🙂

This is a good example that shows why there should always be sample files uploaded with explanations, with a manual result displaying the desired outcome. The solution will come much faster, a description of the data will never be better than a sample file 🙂

Cheers,

Catalin

Hi Catalin,

Yes you are right.

Now i have another trouble again, i linked up the pivot table with my database server data, now the slicers does not show up the other pivot table connections from other tabs.

Last time you changed from the defined table into pivot table (by choosing data source from the defined table to create the pivot table right?), this way able to show all the pivot connections.

But if directly link up the pivot table with database,the slicer only show the pivot connection on that page only.I am not sure why.

Attached is the sample code which i connect with mysql on production live database.Not sure you able to view it.Can you guide me again please?

Thanks & Regards,

Hooi Thin

If you connect the pivot table to an external data source, it will have a different pivot cache then the rest of the pivot tables that are using the internal data table as the data source. I mentioned before that the pivot tables must have the same data source if you want to control them with the same slicers, in this case they are not the same.

Why don't you use the same data source? If you want to use the data connection directly in the pivot table, to that for all pivot tables.

If you want to use the imported data table as the source, do that for ALL pivot table reports. If you mix the data sources, you will not be able to do what you need, without complications.

I would use Power Query to connect to MySQL database, load data as connection only, then use that connection to create any reports you want.

What version of excel are you using?

Catalin

Hi Catalin,

I am using excel 2013, pro plus. 

Now i am trying the rest pivot tables to connect directly to database but the pivot tables won't sum now.I've search solution might be the "total cost" column in the MySQL database was VARCHAR. Then i've changed the datatype to integer,double,numeric,decimal data type but having error of data truncated issue.The total cost value consist from 0.00000343 until 14148 and i think the error happened because of the comma value(eg: 1,345)

Do you know which data type to store this kind of integers as well?

I've heard of power pivot but not power query.I know them is power tool on excel but not really know how to use them.

For my case, if i do not want to connect external data source into pivot table and not imported table for source(the file will be very big size as pivot and table is same data),the only way is power query? 

I just tried to connect power query with mysql, on the setting going to the windows and database authentication there,i misclicked the next button without go to the database part to put server credentials. It cant roll back anymore to reset back after i click next, and keep stuck at the error of 

Details: "Keyword not supported.
Parameter name: integratedsecurity"

I tried to uninstall the power query also same error 🙁

I am in another big issue again, would like to seek your help again.

Thanks & Regards,

Hooi Thin