Run-time error 1004 for Refresh Pivot Table

问题:

I created macro which would refresh all pivot tables in a worksheet. The same code works on one sheet however doesn't on the other. I tried to run it in debug mode and it works fine for first few tables and crush on one in the middle with error:

Run-time error '1004': RefreshTable method of PivotTable class failed.

There are 20 Pivot Tables in this sheet, but on the one where it works are 26. Your help would be much appreciated.

Sub Refresh_Pivots2()

Dim PL As PivotTable

Worksheets("Incidents Pivots").Activate

    For Each PL In ActiveSheet.PivotTables

    PL.RefreshTable

    Next PL

End Sub

I just tried another code

Sub Refresh_Incidents()

Worksheets("Incidents Pivots").Activate

Dim A

A = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")

For i = 0 To 19

ActiveSheet.PivotTables(A(i)).RefreshTable

Next i

End Sub

if I start from i=0 it gives an error but when switch it to i=1 it works. Any ideas what is wrong with this table. I am able to refresh it manually.

回答1:

As Rory suggested there was something wrong with source data for one table, it used A1 instead of R1C1 notation. I changed it in Excel Option->Formulas->R1C1 reference style and now macro works.

回答2:

Have you checked the pivot table datasource. also some time this kind of issues happen due to pivot cache. call the below code before refreshing the pivots.

Sub PivotCacheClear()
    Dim pc As PivotCache
    Dim ws As Worksheet
    With ActiveWorkbook
        For Each pc In .PivotCaches
            pc.MissingItemsLimit = xlMissingItemsNone
        Next pc
    End With
End Sub 

回答3:

I have a code that auto adjust the data from a Pivot Table and then refresh the Pivot Table, may be it can halp you:

Sub AdjustPivotDataRange(ByVal SheetName As String)

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String

  Sheets("All 2014 to Current Date").Visible = True
'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = Worksheets("All 2014 to Current Date")
  Set Pivot_sht = Worksheets(SheetName)

'Enter in Pivot Table Name
  PivotName = "PivotTable1"

'Dynamically Retrieve Range Address of Data
  Set StartPoint = Data_sht.Range("A6")
  LastRow = Data_sht.Cells(Rows.Count, 1).End(xlUp).Row
  LastColumn = Data_sht.Cells(LastRow, Columns.Count).End(xlToLeft).Column
  Data_sht.Activate
  Set DataRange = Data_sht.Range(StartPoint, Cells(LastRow, LastColumn))

  NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
  If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If

'Change Pivot Table Data Source Range Address
  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
  Pivot_sht.PivotTables(PivotName).RefreshTable
  Sheets("Rolling 12-mo.").Select
  Sheets("All 2014 to Current Date").Visible = False
'Complete Message
  'MsgBox PivotName & "'s data source range has been successfully updated!"

End Sub

回答4:

'connection property'=>'usage'=>'enable background refresh', please make sure, that it has not been selected.