[[20211213232649]]　『VBAでピポットテーブルのフィールド名を変更したａx（ハッチ）　ページの最後に飛ぶ

『VBAでピポットテーブルのフィールド名を変更したい』（ハッチ）

VBAで担当者の経費、交通費、交際費をまとめるピボットテーブルを作成しています。
通常は下記コードでまとまるのですが、
交際費が全員0の場合、「合計/交際費」のところが「個数/交際費」になってしまい、
フィールド名を変更する時に『PivotFieldsが取得できません』となります。(★の箇所)

「合計/交際費」でも「個数/交際費」でも"交際費1"と変更できるコード
または、常に合計で処理するようなコードがありましたら教えてください。
宜しくお願い致します。

Sub Pivot()

    Dim ws As Worksheet
    Set ws = Worksheets("東京")

    Worksheets.Add(after:=ws).Name = "まとめ"
    ThisWorkbook.PivotCaches.Create(xlDatabase, ws _
     .Range("A1:D10")).CreatePivotTable Sheets("まとめ").Range("A3"), "1"

    With ActiveSheet.PivotTables("1")
        .PivotFields("担当").Orientation = xlRowField
        .PivotFields("経費").Orientation = xlDataField
        .PivotFields("交通費").Orientation = xlDataField
        .PivotFields("交際費").Orientation = xlDataField

        .PivotFields("合計 / 経費").Caption = "経費1"
        .PivotFields("合計 / 交通費").Caption = "交通費1"
        .PivotFields("合計 / 交際費").Caption = "交際費1" '★
    End With

End Sub

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

AddDataFieldで最初からCaptionを指定してみるのはどうでしょうか。
（きまぐれおじさん） 2021/12/14(火) 08:48

 Sub Pivot()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim pvC As PivotCache
    Dim pvF As PivotField
    Dim tmp As String
    Set ws1 = Worksheets("東京")
    Set ws2 = Worksheets.Add(after:=ws1)
    ws2.Name = "まとめ"

    Set pvC = ThisWorkbook.PivotCaches.Create(xlDatabase, ws1.Range("A1").CurrentRegion)
    With pvC.CreatePivotTable(ws2.Range("A3"))
        For Each pvF In .PivotFields
            With pvF
                If .Name = "担当" Then
                    .Orientation = xlRowField
                Else
                    tmp = .Name
                    .Orientation = xlDataField
                    .Function = xlSum
                    .Caption = tmp & 1
                End If
            End With
        Next
    End With
 End Sub

こんな感じでもいけますね
（きまぐれおじさん） 2021/12/14(火) 09:52

ありがとうございます！
ご提示いただいたコードでもできました！
もうひとつお聞きしたいのですが、行ラベルに『部署』があったとして
ここのフィールド設定で小計をなしにしたい場合はどうしたらいいでしょうか。

Sub Pivot()

    Dim ws As Worksheet
    Set ws = Worksheets("東京")
    Worksheets.Add(after:=ws).Name = "まとめ"
    ThisWorkbook.PivotCaches.Create(xlDatabase, ws _
     .Range("A1:D10")).CreatePivotTable Sheets("まとめ").Range("A3"), "1"
    With ActiveSheet.PivotTables("1")
        .PivotFields("部署").Orientation = xlRowField　'★
        .PivotFields("担当").Orientation = xlRowField
        .PivotFields("経費").Orientation = xlDataField
        .PivotFields("交通費").Orientation = xlDataField
	With .PivotFields("交際費")　
            .Orientation = xlDataField
            .Caption = "交際費1"
        End With

        .PivotFields("合計 / 経費").Caption = "経費1"
        .PivotFields("合計 / 交通費").Caption = "交通費1"

    End With
End Sub
（ハッチ） 2021/12/14(火) 10:19

 .PivotFields("部署").Subtotals(1) = False

を書き加えてください。

（きまぐれおじさん） 2021/12/14(火) 10:43

できました！！！
本当にありがとうございました！
（ハッチ） 2021/12/14(火) 11:50

コメント返信：

[ 一覧(最新更新順) ]

[[20211213232649]] 『VBAでピポットテーブルのフィールド名を変更したａx（ハッチ） ページの最後に飛ぶ

[[20211213232649]]　『VBAでピポットテーブルのフィールド名を変更したａx（ハッチ）　ページの最後に飛ぶ