Announcement

Collapse
No announcement yet.

Pivot tables will not refresh on first try

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pivot tables will not refresh on first try

    I have a macro code that runs a MSQuery returning a table of data to a specified worksheet. I have a pivot table (on a separate worksheet) that uses that table as source data. The same macro that runs the MSQuery also refreshes the pivot table. For some reason the macro must be run twice to get the pivot table to refresh.

    The table of data is imported just fine on the first run. Not sure what the issue is. I have tried multiple variations of code to refresh the pivot tables. I even put the code in twice.

    Thanks for any help.
    dgott

  • #2
    Re: Pivot tables will not refresh on first try

    I have two macros that I use often, one to refresh pivot tables, another to refresh queries, you could try to call them at the end of your code

    Code:
    Public Sub RefreshQueryTables()
        Dim w As Worksheet
        Dim q As QueryTable
        On Error Resume Next
        For Each w In ActiveWorkbook.Worksheets
            Application.StatusBar = "Refreshing Query Table: " & w.Name
            For Each q In w.QueryTables
                q.Refresh
            Next
        Next
    End Sub
    
    Public Sub RefreshPivotTables()
        Dim w As Worksheet
        Dim p As PivotTable
        Dim pf As PivotField
        Dim pi As PivotItem
        On Error Resume Next
        For Each w In ActiveWorkbook.Worksheets
            Application.StatusBar = "Refreshing Pivot Table: " & w.Name
            For Each p In w.PivotTables
                For Each pf In p.PivotFields
                    For Each pi In pf.PivotItems
                       pi.Delete
                    Next
                Next
                p.RefreshTable
                p.Update
            Next
        Next
    End Sub

    Comment


    • #3
      Re: Pivot tables will not refresh on first try

      I'm having the same issue. I tried splitting out the pivot table refreshing code into a separate Sub and calling it. I even tried calling it twice (from within another subroutine). But, still, not all of my pivot tables refresh. I put a MsgBox in the pivot table refresh routine to tell me what pivot table it was refreshing, and it says it's refreshing the ones that aren't updating. But, if I manually refresh the pivot table after the macro runs...it then updates.

      If I run the macro after it runs the first time (and doesn't update), it then updates.

      The data that the pivot table refers to is in a table that is refreshed earlier in the macro from a database connection. That's refreshing fine.

      I'm stumped.


      Originally posted by CorvetteLover View Post
      I have two macros that I use often, one to refresh pivot tables, another to refresh queries, you could try to call them at the end of your code

      Code:
      Public Sub RefreshQueryTables()
          Dim w As Worksheet
          Dim q As QueryTable
          On Error Resume Next
          For Each w In ActiveWorkbook.Worksheets
              Application.StatusBar = "Refreshing Query Table: " & w.Name
              For Each q In w.QueryTables
                  q.Refresh
              Next
          Next
      End Sub
      
      Public Sub RefreshPivotTables()
          Dim w As Worksheet
          Dim p As PivotTable
          Dim pf As PivotField
          Dim pi As PivotItem
          On Error Resume Next
          For Each w In ActiveWorkbook.Worksheets
              Application.StatusBar = "Refreshing Pivot Table: " & w.Name
              For Each p In w.PivotTables
                  For Each pf In p.PivotFields
                      For Each pi In pf.PivotItems
                         pi.Delete
                      Next
                  Next
                  p.RefreshTable
                  p.Update
              Next
          Next
      End Sub

      Comment


      • #4
        Re: Pivot tables will not refresh on first try

        I found this after considerable looking and it solved my problem of the refresh not working all the time.
        From Foxden: I had this problem too and what you need to do is change the setting of your data connections. By default, Excel will "Enable background refresh". This must be turned off and it will force the macro to complete the refresh task before it moves on. If using Excel 2007, bring up the "Connection Properties" and uncheck the box that enables the background refresh. You can get there by clicking on the table that gets refreshed and where you would click to actually refresh the data you should see an arrow for more options.

        Your solutions only worked because they by chance, gave excel enough time to refresh before moving on. Doing what I have suggested is a far more definitive approach and will reduce bugs moving forward with your coding. I hope this helps, from one programmer to another.

        Comment

        Working...
        X