locked
Refreshing Pivot Table Group without Refresh All RRS feed

  • Question

  • I have a set of Pivot tables. Sheets 1-10 all named PivotTable1 connected to the same data connection (usmdata). 

    The pivot tables 1-5 and 6-10 are set up differently. I have vba set up to select a new date for the pivot tables and pivot tables 1-5 change the data appropriately. 6-10 do not. I have noticed that refreshing the data connection fixes the problem of incorrect data but it always performs a refresh all and I only need tables 6-10 refreshed. The refresh all takes a long time and always errors out after 13 minutes with the option to keep going. I have no idea how long after it takes to refresh after that. 

    Is there a way using vba that I can refresh pivot tables 6-10  without refreshing the entire workbook?


    Monday, April 14, 2014 4:54 PM

Answers

  • Just so you know, if you don't want to recalculate EVERYTHING, you can recalculate 1 sheet, or even a Range.

    'recalculate all open workbooks
    Application.Calculate
    
    'recalculate a specific worksheet
    Worksheet(1).Calculate
    
    ' recalculate a specific range
    Worksheet(1).Columns(1).Calculate


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Qaspec Thursday, April 24, 2014 10:08 PM
    Friday, April 18, 2014 8:55 PM
  • Hi Qaspec,

    Through VBA, you could use PivotTable.ChangePivotCache Method (Excel) to refresh one pivot table.

    I created two pivot tables with the same data source (A1:B4) in Excel 2013:

    I changed B with 40 age, after running the code below:

    Sub test1()
        Dim s As Worksheet
        Set s = ActiveSheet
        s.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=s.PivotTables("PivotTable1").SourceData, Version:= _
            xlPivotTableVersion15)'please note that xlPivotTableVersion15 is used for Excel 2013
    End Sub
    

    Only “Pivot Table1” refreshed:


    In this case, you could use the same way to refresh any pivot table without refreshing all pivot tables.

    Hope this helps.


    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 George Hua Tuesday, April 22, 2014 9:42 AM
    Tuesday, April 15, 2014 7:12 AM

All replies

  • Hi Qaspec,

    Through VBA, you could use PivotTable.ChangePivotCache Method (Excel) to refresh one pivot table.

    I created two pivot tables with the same data source (A1:B4) in Excel 2013:

    I changed B with 40 age, after running the code below:

    Sub test1()
        Dim s As Worksheet
        Set s = ActiveSheet
        s.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=s.PivotTables("PivotTable1").SourceData, Version:= _
            xlPivotTableVersion15)'please note that xlPivotTableVersion15 is used for Excel 2013
    End Sub
    

    Only “Pivot Table1” refreshed:


    In this case, you could use the same way to refresh any pivot table without refreshing all pivot tables.

    Hope this helps.


    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 George Hua Tuesday, April 22, 2014 9:42 AM
    Tuesday, April 15, 2014 7:12 AM
  • Just so you know, if you don't want to recalculate EVERYTHING, you can recalculate 1 sheet, or even a Range.

    'recalculate all open workbooks
    Application.Calculate
    
    'recalculate a specific worksheet
    Worksheet(1).Calculate
    
    ' recalculate a specific range
    Worksheet(1).Columns(1).Calculate


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Qaspec Thursday, April 24, 2014 10:08 PM
    Friday, April 18, 2014 8:55 PM
  • Thank you very much. This is a great solution and keeps me from having to move one of my pivot tables to a separate table (just to use another process to copy and paste values back into a sheet in the workbook). 
    Thursday, April 24, 2014 10:09 PM