+ Reply to Thread
Results 1 to 4 of 4

VBA Disconnecting Slicer, Updating PivotTable Source, Reconnecting Slicer

  1. #1
    Registered User
    Join Date
    08-22-2019
    Location
    Wisconsin
    MS-Off Ver
    Office 365
    Posts
    2

    VBA Disconnecting Slicer, Updating PivotTable Source, Reconnecting Slicer

    Hi guys,

    I'm running into a little but of a problem that I'm hoping someone here can help me with.

    On a daily report that I run I have 8 different PivotCharts from 8 different PivotTables, all from the same data source but displaying different parts of it. I also have one slicer and one timeline that are used to filter all 8 charts.

    Here is my problem: Given that the report is updated daily, I have written a macro that automatically updates the pivot tables to include the newly added data. When there is no slicer or timeline it runs fine however when I add them in it gives me a Run Time Error 5.

    Here is my question: What would be the best method for writing a vba code that will 1) Disconnect 8 pivot tables from a slicer that already exists 2) Update the data range on the pivot tables 3) Reconnect each pivot table back to the original slicer?

    I've seen a few solutions through research but nothing that's exactly what I need and I'm tearing my hair out trying to figure it out. Hoping there's an obvious solution.

    Thanks in advance!

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: VBA Disconnecting Slicer, Updating PivotTable Source, Reconnecting Slicer

    Do it in Excel with the macro recorder on, that will give you the code.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA Disconnecting Slicer, Updating PivotTable Source, Reconnecting Slicer

    I'd recommend using PowerQuery (Get & Transform), and basing your pivot table off of data model.

    This should eliminate the need for VBA (Simple click of RefreshAll, or set query property to refresh on interval/workbook open).

    And will avoid issue with connected slicers.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    08-22-2019
    Location
    Wisconsin
    MS-Off Ver
    Office 365
    Posts
    2

    Re: VBA Disconnecting Slicer, Updating PivotTable Source, Reconnecting Slicer

    Quote Originally Posted by Bob Phillips View Post
    Do it in Excel with the macro recorder on, that will give you the code.
    This did it! Wasnt sure it would work like I needed it to based on others experiences which is why I hadnt tried it yet. Should have started here first.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. After updating PivotTable Source data with Macro, slicer doesn't show PivotTable
    By mrdouglaswee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2018, 02:10 AM
  2. Loop through slicer, but don't do slicer action when values are unselected
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2018, 04:55 PM
  3. Loop through slicer, while also selecting single slicer value on separate slicer
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2018, 01:41 PM
  4. Replies: 1
    Last Post: 04-23-2018, 09:43 AM
  5. VBA : Updating Pivot Source = Lost Slicer Connection
    By flipsyd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-04-2014, 06:17 PM
  6. [SOLVED] Change Chart Title Depending on a Slicer - slicer advice
    By JungleJme in forum Excel General
    Replies: 8
    Last Post: 08-17-2012, 07:59 AM
  7. Excel 2007 : Use a Slicer From Another Pivottable
    By Hashiru in forum Excel General
    Replies: 1
    Last Post: 01-17-2012, 04:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1