excel - 默认情况下选择刷新时添加的 VBA PivotItems

Question

当我刷新数据，然后使用以下代码刷新数据透视表时，默认情况下会选择新添加的项目。有什么方法可以防止这种情况发生，因为这意味着我必须再次进入并取消选择？

    Dim pt As PivotTable
    Dim pf As PivotField
    For Each pt In Worksheets("Summary").PivotTables

    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh

    For Each pf In pt.PivotFields
       pf.AutoSort xlAscending, pf.Name
    Next pf
    Next pt

    Set pf = Nothing
    Set pt = Nothing

Answer 1

根据我上面的评论，这样的事情应该有效。如果您有很多需要检查的数据透视字段，您可能可以创建一些函数来使其更容易或更高效。

Sub pt()


Dim pt As PivotTable, pf As PivotField, pi As PivotItem
Dim dict As Dictionary 'requires reference to Microsoft Scripting Runtime

For Each pt In Worksheets("Summary").PivotTables

    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

    Set dict = New Dictionary

    ' you will need to loop through each Fields PivotItem Collection separately 
    For Each pi In pt.PivotFields("myPivotField").PivotItems
        dict.Add pi.Name, 1
    Next

    pt.PivotCache.Refresh

   'again, you will need to loop through each one separately to check
    For Each pi In pt.PivotFields("myPivotField").PivotItems
        If dict.Exists(pi.Name) Then Else: pi.Visible = False
    Next

    For Each pf In pt.PivotFields
       pf.AutoSort xlAscending, pf.Name
    Next pf

Next pt

Set pf = Nothing
Set pt = Nothing

End Sub