Excel 2007 VBA - 数据透视表字段列表???造成错误

Last update on October 14, 2011 - I know more info now, so I will summarize everything here and delete all my previous descriptions/questions.

2011年10月14日的最新更新 - 我现在了解更多信息,因此我将在此汇总所有内容并删除我之前的所有描述/问题。

The problem is, after running a certain program, I get some weird error. It's similar to, but not exactly like, when I click "Split". The difference is the solid gray bars do not show up. It's sort of weird. More recently, I have started thinking it is probably caused by the field list of the pivot tables involved.

问题是,在运行某个程序后,我得到了一些奇怪的错误。当我点击“拆分”时,它类似于,但不完全一样。不同的是实心灰色条不显示。这有点奇怪。最近,我开始认为它可能是由所涉及的数据透视表的字段列表引起的。

The problem occurs with any of the three following pieces of code

以下三个代码中的任何一个都会出现问题

Code 1

Dim pvtCache As PivotCache
For Each pvtCache In ThisWorkbook.PivotCaches
    pvtCache.Refresh
Next pvtCache

Code 2

ThisWorkbook.RefreshAll

Code 3

Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
Next ws

But not with this, where the only difference is I added ws.Activate to Code 3

但不是这个,唯一的区别是我添加了ws.Activate到Code 3

Code 4

Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
Next ws

So, my question is, can any one explain this? Can any one fix Code 1 (which is much faster in a spreadsheet with 2 pivot caches from large data sources, but 46 pivot tables) to make it work without this error?

所以,我的问题是,任何人都能解释一下吗?任何人都可以修复代码1(在具有来自大型数据源的2个透视缓存的电子表格中更快,但是有46个数据透视表)以使其在没有此错误的情况下工作吗?

Thanks for any help!!!

谢谢你的帮助!!!

2 个解决方案

#1

I have multiple pivots from multiple sheets and all three code snippets works fine. There should be something else causing the problem, its not the code.

我有多个工作表的多个枢轴,所有三个代码片段工作正常。应该有其他原因导致问题,而不是代码。

#2

Your code snippets run correctly.

您的代码段正确运行。

It could be that the screen display may not be refreshing after your code is run.

可能是您的代码运行后屏幕显示可能无法刷新。

Try adding this round your code:

尝试添加此代码:

Application.ScreenUpdating = False
    ''//Your code here
DoEvents
Application.ScreenUpdating = True

#1