• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

macro to detach all slicers from all pivot tables|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / macro to detach all slicers from all pivot tables|VBA & Macros|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & Macros macro to detach all slicers from a…
sp_PrintTopic sp_TopicIcon
macro to detach all slicers from all pivot tables
Page: 12345Jump to page
Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
1
July 11, 2016 - 4:29 pm
sp_Permalink sp_Print

Hi,

I have an excel 2010 workbook with a worksheet named Details. And have slicers with one table connect with external data source from ms access.Output is tables rows and columns.

Then I have another sheet named Chart  where i create the new pivot tables and slicers (data source is from Details.)-output is charts

The two sheet is independent with each other for different slicer but with same source.The new pivots can't be associated to old slicers.

How do i use macro to connect all the pivot tables with slicers(the slicer can connect with Chart data as well).

I have search around regarding this and found some need to change the pivot cache but i do not know how to use it.

I am new on macro ,perhaps a tutorial will be great for me to learn.Thanks and appreciate if anyone can help!

 

Regards,

Hooi Thin

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
July 14, 2016 - 1:39 pm
sp_Permalink sp_Print sp_EditHistory

Hi Hooi,

You can try this macro to disconnect all slicers:

Sub DisconnectAllSlicers()

Dim SlicersDict As Variant

Set SlicersDict = CreateObject("Scripting.Dictionary")

Dim sl As SlicerCache, slpt As PivotTable, SlItem As Variant

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

'take each slicer
For Each SlItem In SlicersDict.Keys
    'remove pt connections for this slicer
    For Each slpt In SlicersDict(SlItem) 'for each pivot table controlled by this slicer,
        slpt.SaveData = True
        ThisWorkbook.SlicerCaches(SlItem).PivotTables.RemovePivotTable (slpt)
    Next slpt
Next SlItem

Set SlicersDict = Nothing
End Sub

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

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
3
July 14, 2016 - 8:53 pm
sp_Permalink sp_Print

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!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
July 15, 2016 - 3:27 am
sp_Permalink sp_Print sp_EditHistory

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:

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim SlItem As SlicerItem
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems ' this field is from pivot table slicer
    ThisWorkbook.SlicerCaches("Slicer_Field11").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
End Sub

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

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
Dim SlItem As SlicerItem
MsgBox 1
For Each SlItem In ThisWorkbook.SlicerCaches("Slicer_Field11").SlicerItems 'this field is from table slicer
ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems(SlItem.Name).Selected = SlItem.Selected
Next
End Sub

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

sp_AnswersTopicAnswer
Answers Post
Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
5
July 15, 2016 - 7:18 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
July 15, 2016 - 9:38 pm
sp_Permalink sp_Print

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:

ThisWorkbook.SlicerCaches("Slicer_Field1").SlicerItems(SlItem.Name).Selected = SlItem.Selected

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

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
7
July 16, 2016 - 1:12 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
July 16, 2016 - 3:57 am
sp_Permalink sp_Print

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

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
9
July 18, 2016 - 3:58 am
sp_Permalink sp_Print sp_EditHistory

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 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
July 18, 2016 - 7:47 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
11
July 18, 2016 - 12:26 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
July 18, 2016 - 1:18 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
13
July 19, 2016 - 5:35 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
14
July 19, 2016 - 6:16 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
15
July 19, 2016 - 6:58 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
16
July 20, 2016 - 2:13 am
sp_Permalink sp_Print

Hi Hooi,

You have to provide more details. In the file you mentioned, there is no code for connecting to the database, I hoped to find there the information I need to understand the situation.

Is it a local database, on your computer? If so, you need to type localhost as the server name, in the next field type the database name.

In the next window, click on Database (not Windows authentication), and type your access credentials, if it's a protected database. You will then be able to select the tables you want from the database.

If it's not a local server, just type the IP of that server as the server name, with the database name.

Make sure that your IP is whitelisted on that computer, but I don't think you have a problem with that, as you mentioned that you are able to connect and extract data from that server.

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
17
July 20, 2016 - 2:54 am
sp_Permalink sp_Print

Hi Catalin,

Its not local database,i am connecting to our company's live database.

Oh..IP an alternative way, why can't i think that 😀 can connect now finally thank you..i can connect to the power query now.

So i should just create the connection from the power query only right? And i will make the power query workbook as the data source.

Now come back the same issue for the sum of pivot table is not working. The data type of the total cost column on databse consist of integer with comma, currently is saved as varchar so it can't be sum up. Can you help for this?

Thanks & Regards,

Hooi Thin

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
18
July 20, 2016 - 3:08 am
sp_Permalink sp_Print

Yes, from Power Query tab, choose New Query-From Database-MySQL Database, type the IP and database name, then the credentials in the next window.

After selecting the table you want, choose Close and Load To, then in that window select Connection Only. (this will not load the data into worksheet).

When you create the pivot tables, instead of selecting a range, click the button:  From Connection, and you should be able to find the new query listed there.

Use that connection to create all the reports you need.

Where can I see the data errors you mentioned? Can't work with data from my memory 🙂 , you have to upload sample files.

Power Query has very powerful tools to cleanup and reformat the data imported from any source, use them to reformat the data.

Avatar
yhooithin05
Member
Members
Level 0
Forum Posts: 61
Member Since:
July 11, 2016
sp_UserOfflineSmall Offline
19
July 20, 2016 - 12:08 pm
sp_Permalink sp_Print

Yes. I can set up the connection on power query now 🙂

Attached sample file for your reference. Its great if can reformat the data from text to integer on power query without do anything on the database.

Catalin can you guide me? 🙂

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
20
July 20, 2016 - 1:24 pm
sp_Permalink sp_Print sp_EditHistory

You have to go to Power Query tab in Ribbon, click on Show Queries to view the Queries Pane, right click the query and select Edit (or just double click the query). This will open the Power Query window, with its own ribbon and tools. To reformat a column, right click that column , select ChangeType, you will have many options there.

Explore the menu, there are many useful transformations that can be done, including adding columns with specific calculations, replacing values, and so on.

See these tutorials for more informations: power+query+site:www.myonlinetraininghub.com

If the file is going to be used by other users, they need to have power query installed to refresh the data. ON the first use of the file, they should open the query and enter their credentials to access the database, data cannot be seen without that. The credentials are not stored in the file, they are stored in user's computer.

You can also join the Power Query course, this will be the standard power tools for data import and processing in excel.

Page: 12345Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Victoria Turner
Guest(s) 39
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Richard Adams
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Forum Stats:
Groups: 3
Forums: 24
Topics: 6080
Posts: 26686

 

Member Stats:
Guest Posters: 49
Members: 31571
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.