刷新数据透视表的运行时错误1004 [英] 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:

运行时错误'1004':数据透视表类的RefreshTable方法 失败.

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

此工作表中有20个数据透视表，但在其中一个有效的数据透视表中有26个.非常感谢您的帮助.

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

如果我从i = 0开始，它将给出一个错误，但是当将其切换到i = 1时，它将起作用.任何想法此表有什么问题.我可以手动刷新.

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.

推荐答案

正如罗里(Rory)所建议的那样，一张表的源数据有问题，它使用A1而不是R1C1表示法.我以Excel Option-> Formulas-> R1C1参考样式对其进行了更改，现在可以使用宏了.

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.

这篇关于刷新数据透视表的运行时错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！