excel 透视表 vba_Excel VBA：在特定数据透视表上运行宏

excel 透视表 vba

Last week, I posted Bob Ryan's Excel macro for formatting a pivot table in Classic style. Bob's macro formats the first pivot table indexed on the active sheet.

上周，我发布了Bob Ryan的Excel宏，用于格式化古典风格的数据透视表 。 鲍勃的宏格式化活动工作表上索引的第一个数据透视表。

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)

Ideally, you'd only have one pivot table on a worksheet, to prevent problems with overlapping, and Bob's code would work very well. However, as you know, life in Excel isn't always ideal!

理想情况下，工作表上只有一个数据透视表，以防止出现重叠问题，Bob的代码可以很好地工作。 但是，您知道，Excel中的生活并不总是理想的！

Let's look at a few scenarios, and how to modify the macro to deal with them.

让我们看一些情况，以及如何修改宏以处理它们。

选择数据透视表 (Select a Pivot Table)

In the blog post comments, Yard suggested a variation on the code, so the macro would run on the selected pivot table, to accommodate worksheets with multiple pivot tables.

在博客文章评论中，Yard建议对代码进行更改，以便宏可以在所选的数据透视表上运行，以容纳具有多个数据透视表的工作表。

If a cell in a pivot table isn't selected, an "Oops" message would be displayed.

如果未选中数据透视表中的单元格，则会显示“糟糕”消息。

    On Error Resume Next
    Set PT = ActiveCell.PivotCell.PivotTable
    On Error GoTo 0
    If PT Is Nothing Then
        MsgBox "No PivotTable selected", vbInformation, "Oops..."
        Exit Sub
    End If

Thanks, Yard, for your sample code. On a multiple pivot table sheet, the user can control which pivot table is formatted.

谢谢，Yard，您的示例代码。 在多个数据透视表上，用户可以控制格式化哪个数据透视表。

格式化活动工作表上的所有数据透视表 (Format All Pivot Tables on Active Sheet)

Taking that idea a bit further, let's assume you have a worksheet with several pivot table on it. With Yard's code, shown above, you could select a cell in one of those pivot tables, and run the macro to format that pivot table only.

进一步说明一下，让我们假设您有一个包含多个数据透视表的工作表。 使用上面显示的Yard的代码，您可以在那些数据透视表之一中选择一个单元格，然后运行宏以仅格式化该数据透视表。

But, what if you wanted to format all the pivot tables on that sheet? It would take a while to select each pivot table, and run the macro. Instead, you could modify the code, so it formats all the pivot tables on the active sheet.

但是，如果要格式化该工作表上的所有数据透视表怎么办？ 选择每个数据透视表并运行宏将花费一些时间。 相反，您可以修改代码，以使其格式化活动工作表上的所有数据透视表。

    For Each PT in ActiveSheet.PivotTables
        'the formatting code goes here
    Next PT

格式化所有工作表上的所有数据透视表 (Format All Pivot Tables on All Worksheets)

Finally, what can you do if there's more than one worksheet with pivot tables? You don't want to waste time selecting each worksheet, and running the macro to format all the pivot tables on that sheet.

最后，如果有多个包含数据透视表的工作表，该怎么办？ 您不想浪费时间选择每个工作表，并运行宏来格式化该工作表上的所有数据透视表。

To loop through the worksheet, you could modify the code, so it formats all the pivot tables on each worksheet in the active workbook.

要遍历工作表，您可以修改代码，以便在活动工作簿中格式化每个工作表上的所有数据透视表。

    Dim ws as Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For Each PT in ws.PivotTables
            'the formatting code goes here
        Next PT
    Next ws

翻译自: https://contexturesblog.com/archives/2010/08/25/excel-vba-run-macro-on-specific-pivot-tables/

excel 透视表 vba