+ Reply to Thread
Results 1 to 7 of 7

Update pivot tables without refreshing power query

  1. #1
    Registered User
    Join Date
    03-20-2019
    Location
    North East
    MS-Off Ver
    Windows 10
    Posts
    4

    Update pivot tables without refreshing power query

    I have a workbook with numerous worksheets with data from these feeding 5 pivot tables on 4 of these sheets.
    I am trying to produce some vba which will update the pivot tables when there is a worksheet change.
    Is it possible to do this at workbook level, defining the sheets to be refreshed when a change occurs. Also there is a power query in the workbook that I don’t want to update.
    Hope someone can help!

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Update pivot tables without refreshing power query

    You can either update pivot tables specifically by name:

    Please Login or Register  to view this content.
    or you can update all pivotcaches in the workbook in a loop:

    Please Login or Register  to view this content.
    Rory

  3. #3
    Registered User
    Join Date
    03-20-2019
    Location
    North East
    MS-Off Ver
    Windows 10
    Posts
    4

    Re: Update pivot tables without refreshing power query

    Can the loop variant be applied at workbook level or does it need to be on the relevant data source worksheet? I’m Trying to apply at workbook level and for it to run on any sheet change.
    I am, however, brand new to vba so please tell me if I’m barking up the wrong tree!

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Update pivot tables without refreshing power query

    The loop is working at the workbook level.

  5. #5
    Registered User
    Join Date
    03-20-2019
    Location
    North East
    MS-Off Ver
    Windows 10
    Posts
    4

    Re: Update pivot tables without refreshing power query

    i have applied the suggested loop on the 'this workbook (code)' sheet as follows;

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    For Each pc In ActiveWorkbook.PivotCaches
    pc.Refresh
    Next
    End Sub

    It does not seem to automatically update the pivot table when some data is changed on an associated input sheet. Have I done something incorrectly?

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Update pivot tables without refreshing power query

    Put a breakpoint or message in the code so you can see if it’s being triggered at all. Also note that formula changes don’t trigger a Change event.

  7. #7
    Registered User
    Join Date
    03-20-2019
    Location
    North East
    MS-Off Ver
    Windows 10
    Posts
    4

    Re: Update pivot tables without refreshing power query

    Thanks rorya. Was the wrong type of event I was using. Sheet activate/deactivate did the trick!

+ 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. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  2. [SOLVED] Refreshing only one power query query
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2019, 06:37 AM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. refreshing in power query
    By BORUCH in forum Excel General
    Replies: 3
    Last Post: 11-07-2017, 12:04 AM
  5. Refreshing Query Tables
    By NatalieEC in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2015, 05:21 PM
  6. Pivot tables and refreshing query
    By vikki2906 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2009, 08:21 AM
  7. [SOLVED] Refreshing MS Query and Excel Pivot Tables using VBA
    By hoppermr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2005, 07:10 PM

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