基于字段层次结构的VBA数据透视表格式 [英] VBA pivot table formatting based on field hierarchy

问题描述

我的工作是编写一个宏，该宏可以复制并格式化初步的数据透视表以适合公司范围内的品牌风格.
基本宏已经完成，但是我在根据枢纽字段的层次结构和依存关系自动设置格式方面遇到麻烦.

I was tasked at work to write a macro that copies and formats a preliminary pivot table to fit the company wide branding style.
The basic macro is complete but I am having trouble automating the formatting of the pivot fields based on their hierarchy and dependency.

当前代码如下:

Sub FormatHierarchy()
    'formatting Hierarchy level 1
    ActiveSheet.PivotTables(1).PivotSelect "'EBITDA category'[All]", _
    xlLabelOnly + xlFirstRow, True

    With Selection
        With .Font
            .Name = "Arial Narrow"
            .Size = 10
            .Bold = True
        End With

        .VerticalAlignment = xlTop
        .HorizontalAlignment = xlLeft
        .WrapText = True
    End With


'formatting Hierarchy level 2
ActiveSheet.PivotTables(1).PivotSelect "Account[All]", _
    xlLabelOnly + xlFirstRow, True

    With Selection
        With .Font
            .Name = "Arial Narrow"
            .Size = 10
            .Bold = False
        End With

        .VerticalAlignment = xlTop
        .HorizontalAlignment = xlLeft
        .WrapText = True
        .IndentLevel = 0
    End With

'formatting Hierarchy level 3
ActiveSheet.PivotTables(1).PivotSelect "SuSa account[All]", _
    xlLabelOnly + xlFirstRow, True

    With Selection
        With .Font
            .Name = "Arial Narrow"
            .Size = 10
            .Bold = False
        End With

        .VerticalAlignment = xlTop
        .HorizontalAlignment = xlLeft
        .WrapText = True
        .IndentLevel = 1
    End With

End Sub

"EBITDA类别"，帐户"和"SuSa帐户"将根据原始数据以及经理决定调用它们的方式进行更改，因此我无法直接使用这些名称. 有没有一种方法可以根据其层次结构直接引用字段名称?

"EBITDA category", "Account" and "SuSa account" will change based on the raw data and whatever the manager decides to call them, so I cannot directly use the names. Is there a way to directly reference the field names based on their hierarchy?

原始数据透视表
结果数据透视表(粗体为1级，普通为2级，缩进为3级)

original pivot table
resulting pivot table (bold is level 1, normal is level 2 and indented is level 3)

我们将不胜感激.
谢谢！

Any help is appreciated.
Thanks!

推荐答案

尝试下面的代码，并在代码内部作为注释进行解释:

Try the code below, explanation inside the code as comments:

Option Explicit    

Sub FormatHierarchy()

Dim PvtTbl As PivotTable
Dim PvtFld As PivotField

' set the PivotTable object
Set PvtTbl = ActiveSheet.PivotTables(1)

' loop through all Pivot Fields in Pivot Table
For Each PvtFld In PvtTbl.PivotFields
    Select Case PvtFld.Position
        Case 1
           ' do your format here ...

        Case 2
            ' do your format here ...

        Case 3
            ' do your format here ...

    End Select    
Next PvtFld

End Sub

这篇关于基于字段层次结构的VBA数据透视表格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！