none
Macro to Refresh a pivot table when new data is entered RRS feed

  • Question

  • I tried to write a simple macro to refresh 2 pivot tables whenever any data is changed on a specific worksheet. This code is on the "Scores" worksheet

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        'If any cell is changed on the "Scores" sheet then
        'refresh the pivot tables on "Scores" and "Players"
        'sheets

        Dim pvtTable As PivotTable

        'I4 is the upper left corner of pivot table 1
        Set pvtTable = Worksheets("Scores").Range("I4").PivotTable
        pvtTable.RefreshTable

        'B18 is the upper left corner of pivot table 2
        Set pvtTable = Worksheets("Players").Range("B18").PivotTable
        pvtTable.RefreshTable

    End Sub

    When I paste a range of data (48 cells) into scores I get this error  - "the Method 'RefreshTable' of object 'PivotTable' failed". Then Excel crashes (becomes unresponsive) and I have to kill the app in task manager. 

    So what am I doing wrong?  Thanks

    Monday, October 13, 2014 9:25 PM

Answers

  • Hi,

    >>When I paste a range of data (48 cells) into scores I get this error  - "the Method 'RefreshTable' of object 'PivotTable' failed". <<

    I think the reason is that RefreshTable method of PivotTable object will trigger the Change event of the workbook when the pivot table is in the worksheet.

    So if you edit in the Sheet "Scores" to trigger the change event, it will run into an infinite loop to cause Excel crash because refreshing pivot table (in Range "I4") will trigger the change event of sheet "Scores". You can debug to check this point.

    For a workaround, you can set refreshing of pivot table to be executed only when some specific ranges change such as the bold codes in the sample below, so that it can avoid the pivot table to be continually refreshed. In the sample below, only when we edit in the Range "A1" to "A10", it will refresh the pivot table. Note that, the specific ranges cannot contain the pivot table range.

    Private Sub Worksheet_Change(ByVal Target As Range) 'If any cell is changed on the "Scores" sheet then 'refresh the pivot tables on "Scores" and "Players" 'sheets If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub Dim pvtTable As PivotTable

    ···


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by froggygremblin Tuesday, October 14, 2014 8:44 PM
    Tuesday, October 14, 2014 9:27 AM
    Moderator

All replies

  • Hello,

    I would refresh the data through the pivocache of the tables

    dim pc as pivocache

    for each pc in thisworkbook.pivocaches

    pc.refresh

    next

    Also, if you adress a pivottable, try to adress it with the index or name: Set pvtTable = Worksheets("Scores")..PivotTables(1)

    Tuesday, October 14, 2014 8:17 AM
  • Hi,

    >>When I paste a range of data (48 cells) into scores I get this error  - "the Method 'RefreshTable' of object 'PivotTable' failed". <<

    I think the reason is that RefreshTable method of PivotTable object will trigger the Change event of the workbook when the pivot table is in the worksheet.

    So if you edit in the Sheet "Scores" to trigger the change event, it will run into an infinite loop to cause Excel crash because refreshing pivot table (in Range "I4") will trigger the change event of sheet "Scores". You can debug to check this point.

    For a workaround, you can set refreshing of pivot table to be executed only when some specific ranges change such as the bold codes in the sample below, so that it can avoid the pivot table to be continually refreshed. In the sample below, only when we edit in the Range "A1" to "A10", it will refresh the pivot table. Note that, the specific ranges cannot contain the pivot table range.

    Private Sub Worksheet_Change(ByVal Target As Range) 'If any cell is changed on the "Scores" sheet then 'refresh the pivot tables on "Scores" and "Players" 'sheets If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub Dim pvtTable As PivotTable

    ···


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by froggygremblin Tuesday, October 14, 2014 8:44 PM
    Tuesday, October 14, 2014 9:27 AM
    Moderator
  • Thank you,  That fixed the problem.  Here is the final code that worked

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
        'If any cell is changed on the "Scores" sheet in the "Score" Range
        'then refresh the 2 pivot tables on "Scores" Worksheet and one on the Players Sheet

       
       
         If Intersect(Target, Range("Score")) Is Nothing Then Exit Sub
        
         Dim pvtTable As PivotTable

         'I4 is the upper left corner of pivot table 1
          Set pvtTable = Worksheets("Scores").Range("I4").PivotTable
          pvtTable.RefreshTable

         'I153 is the upper left corner of pivot table 2
          Set pvtTable = Worksheets("Scores").Range("I153").PivotTable
          pvtTable.RefreshTable
         
          'B19 is the upper left corner of pivot table 3
          Set pvtTable = Worksheets("Players").Range("B19").PivotTable
          pvtTable.RefreshTable

    End Sub

    Tuesday, October 14, 2014 9:02 PM
  • Hi, would you be able to put the file link for this document available so I can download it and see if I can set it up to match mine or make mine work.
    Wednesday, July 8, 2015 2:28 PM