遍历工作表，刷新和排序数据透视表 [英] Looping through worksheets, refreshing and sorting pivot tables

问题描述

我试图创建一个宏来循环遍历工作簿中的所有工作表，确定是否有数据透视表，刷新该数据透视表，然后对特定列进行排序.许多工作表无法在特定的列上进行排序，因此我提供了将它们排除在外的代码.

I have tried to create a macro to loop through all worksheets in a workbook, determine if there is a pivot table, refresh that pivot table, and sort on a specific column. There are a number of sheets that won't be sorted on a specific column so I have included code to exclude them.

似乎它只是对 ActiveSheet 上的数据透视表进行排序.我认为这是因为 ActiveSheet.PivotTable(1)部分，但是如果我取出该部分，该代码将不会运行.我是VBA的新手，所以我对下一步的工作一无所知.任何帮助将不胜感激.

It seems that it is only sorting the pivot table on the ActiveSheet. I think this is because of the ActiveSheet.PivotTable(1) piece, but the code won't run if I take that piece out. I'm fairly new to VBA, so I'm at a loss for what to do next. Any help would be greatly appreciated.

Sub RefreshPivotCache()
    Dim ws As Worksheet
    Dim PT As PivotTable

    For Each ws In ActiveWorkbook.Worksheets '<~~ Loop all worksheets in workbook
        If ws.PivotTables.Count > 0 Then
              If (ws.Name <> "L&D TE Summary") And (ws.Name <> "L&D BCD Summary") And (ws.Name <> "HR Ops TE") And (ws.Name <> "HR Ops BCD") And (ws.Name <> "Strat Delivery Summary") _
                And (ws.Name <> "Strat Delivery TE") And (ws.Name <> "Strat Delivery BCD") Then
                    For Each PT In ws.PivotTables        '<~~ Loop all pivot tables in worksheet
                        PT.PivotCache.Refresh
                       ActiveSheet.PivotTables(1).PivotFields("Associate Name").AutoSort _
                         xlDescending, " ", ActiveSheet.PivotTables(1).PivotColumnAxis. _
                         PivotLines(1), 1

                     Next PT
                End If
        End If

    Next ws
End Sub

推荐答案

您仅更改了 Activesheet 上的数据透视表，但从未在您的 ws 中激活环形.所以改变

You're only changing the pivot table on your Activesheet, but you never activate ws in your loop. So change

ActiveSheet.PivotTables(1).PivotFields("Associate Name").AutoSort _
                         xlDescending, " ", ActiveSheet.PivotTables(1).PivotColumnAxis. _
                         PivotLines(1), 1

到

ws.PivotTables(1).PivotFields("Associate Name").AutoSort _
                         xlDescending, " ", ws.PivotTables(1).PivotColumnAxis. _
                         PivotLines(1), 1

这篇关于遍历工作表，刷新和排序数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！