none
Runtime error 1004 When Refreshing Pivot Tables RRS feed

  • Question

  • Using Excel 2010, Windows 7 Professional 64bit, Office 2010, 8gb of ram

    I'm working on automating a spreadsheet with multiple tables, query calls, and pivot tables.  We use ASC Sequel as the query package to access data files from our main system.  I have everything working correctly through the point of having run all the queries and pulled all the data into the different tables.  At that point, I have the macro save the workbook.  After the save, I originally tried to just use the "ActiveWorkbook:RefreshAll" command.  This command generates a "not enough resources" warning.  

    I then broke the macro down to go to each pivot table and refresh just them individually.  At that point I started getting the "runtime error 1004" message.  

    Since the macro saves the file after all the data pulls, I re-opened the file and tried to run the refresh macro by itself.  It will complete once without error messages, but when I try to save the file I get the message that the task can't be completed with available resources, so I can't save the file.  If I try to run the refresh macro again, I get the runtime error 1004 message, and not always on the same pivot table.

    The code I'm using for the individual pivot table refresh routine is:

    Sub RUN_ALL()


    Application.Calculation = xlCalculationManual

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False
    Application.Goto "SS_PIVOT"
        ActiveSheet.PivotTables("S_STOCK").PivotCache.Refresh

    Application.Goto "SRO_PIVOT"
        ActiveSheet.PivotTables("SRO_PVT").PivotCache.Refresh

    Application.Goto "CC_PIVOT"
        ActiveSheet.PivotTables("CC_PVT").PivotCache.Refresh

    Application.Goto "DZ_PIVOT"
        ActiveSheet.PivotTables("DZ_PVT").PivotCache.Refresh

    Application.Goto "DLA_PIVOT"
        ActiveSheet.PivotTables("DLA_PVT").PivotCache.Refresh

    Application.Goto "ORD_PIVOT"
        ActiveSheet.PivotTables("ORD_PVT").PivotCache.Refresh


    Application.Calculation = xlCalculationAutomatic
    Application.Calculation = xlCalculationManual

    Sheets("Pivot Summary Analysis").Select
    Application.ScreenUpdating = True


    End Sub

    Any help or suggestions would be very much appreciated.  If I need to provide more info, let me know.

    jra

    Thursday, July 24, 2014 2:31 PM

All replies

  • Hi jra,

    Is this all the VBA code you have? Does the error occur if you manually refresh the pivottables? And what's the full error message?

    I'd like you to upload the workbook in OneDrive so that I can reproduce the problem. I don't have the problem in my Excel workbook with PivotCache.Refresh function.

    Please Also check the datasource of the pivottables. If the datasource has any change, for example, a new column is added to the dtasource table, runtime error "1004" may occur.

    By the way, also try RefreshTable function:

    Sheet1.PivotTables("PivotTable1").RefreshTable


    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.

    Friday, July 25, 2014 6:18 AM
    Moderator