+ Reply to Thread
Results 1 to 3 of 3

Macro to update pivot tables and use the SAME cache instead of repeatedly recreating cache

  1. #1
    Registered User
    Join Date
    06-22-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    4

    Macro to update pivot tables and use the SAME cache instead of repeatedly recreating cache

    Hi all,

    I have a bunch of reports that require dozens of pivot tables to be updated. The pivot tables within a single report all access the same data source, but across the portfolio of reports there are multiple different data sources (which is why I have the prompt to choose a source data file).

    Some of the reports require the source data to be cached within the workbook so that people can manipulate the data. Other reports do not require the source data to be cached.

    My problem is that when I need the source data cached, it looks like my macro is creating a cache for EACH pivot table even though I'm trying to only get it to create the cache once and have each subsequent pivot table reference that cache. The result is my file size is gigantic.

    Could someone please take a look at my code and tell me what I'm doing wrong and how to fix it? I've highlighted the most relevant sections in orange.

    Key goals:
    Maintain ability to choose different file sources
    Maintain ability to choose whether to turn on/off the Save Data with File
    Create a single cache for each workbook and have all pivot tables source from that cache

    Here is the macro:

    Please Login or Register  to view this content.
    Thanks for the help.
    Last edited by Fowzee1; 06-22-2015 at 11:45 PM.

  2. #2
    Registered User
    Join Date
    06-22-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    4

    Re: Macro to update pivot tables and use the SAME cache instead of repeatedly recreating c

    In case anyone is interested, I figured it out:

    Please Login or Register  to view this content.
    Last edited by FDibbins; 06-22-2015 at 11:27 PM.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Macro to update pivot tables and use the SAME cache instead of repeatedly recreating c

    Fowzee1, welcome to the forum, and thanks for sharing your solution

    For future reference, please use code tags when posting code (see my note below). I will add then for you - this time
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Consolidate existing Pivot Tables to use one cache
    By 4am in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2011, 01:27 PM
  2. Macro to create common cache for groups of pivot tables
    By robhumbug in forum Excel General
    Replies: 0
    Last Post: 12-21-2010, 07:15 PM
  3. Create multiple pivot tables from pivot cache
    By dpatel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2010, 12:24 PM
  4. Create the Pivot cache and pivot macro
    By krishna.chvg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-31-2010, 10:03 PM
  5. pivot tables - set up one pivot cache, and then create tables
    By chalie1105 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-12-2007, 11:07 AM

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