+ Reply to Thread
Results 1 to 2 of 2

Refreshing all Pivot Tables in the On_Open() code

  1. #1
    Tony White
    Guest

    Refreshing all Pivot Tables in the On_Open() code

    Hello
    I have 50 pivot tables( 1 original and 49 copies) that need to be refreshed
    when I open the workbook. Is there a way to write a For Each...Next procedure
    to step through all the pivot tables on a given sheet?
    Thanks!


  2. #2
    Datasort
    Guest

    RE: Refreshing all Pivot Tables in the On_Open() code

    Tony,

    You can set a flag to make each pivottable (cache) update on open. Here is
    some code to set the flag

    For Each pc In ActiveWorkbook.PivotCaches
    pc.RefreshOnFileOpen = True
    Next

    or if you want to do it each time the sheet opens via code


    Sub Macro1()


    Dim ptblcache As PivotCache

    For Each ptblcache In ThisWorkbook.PivotCaches
    ptblcache.Refresh
    Next ptblcache

    Set ptblcache = Nothing

    End Sub

    Best of luck,

    Stewart Rogers


    "Tony White" wrote:

    > Hello
    > I have 50 pivot tables( 1 original and 49 copies) that need to be refreshed
    > when I open the workbook. Is there a way to write a For Each...Next procedure
    > to step through all the pivot tables on a given sheet?
    > Thanks!
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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