[[20190820131358]]　『(Excel2010 VBA)アクティブセルのピボットの初期吹x（Bioferman）　ページの最後に飛ぶ

『(Excel2010 VBA)アクティブセルのピボットの初期設定を変更したい』（Bioferman）

Office2010のVBAにて
アクティブシートのピボットテーブルの設定を変更する
マクロを組もうとしております。
ネット上で紹介されていたマクロを参考に、本質問文末のとおり作成し、
自身の環境で動作することを確認しました。

現状のマクロに、以下2件の要件を追加したいと考えております。
1:値フィールドの集計方法を「合計」に変更する
2:値フィールドのセル書式を3桁カンマ表示（数値）に変更する

色々と試しましたが、
それらしいコードを挿入しても、構文エラーが出てしまいます。
ご指導のほど、よろしくお願い申し上げます。

Sub ピボット修正()

'Keyboard Shortcut: Ctrl+Shift+T

        Dim ピボット
        'シート内のすべてのピボットについて処理を行う
        For Each ピボット In ActiveSheet.PivotTables
        '集計を削除するfor文
        For Each テーブル In ActiveSheet.PivotTables(ピボット.Name).PivotFields
        テーブル.Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)

        Next テーブル
        With ピボット
            .InGridDropZones = True '従来のピボットテーブルレイアウトにする
            .RowAxisLayout xlTabularRow '表形式にする
            .HasAutoFormat = False        '「更新時に列幅を自動調整する」を「オフ」
            .RepeatAllLabels xlRepeatLabels '「アイテムのラベルをすべて繰り返す」を「オン」
        End With

Next ピボット

End Sub

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

 ピボットテーブルのマクロは複雑なので、マクロの自動記録から始めてみては？

（渡辺ひかる） 2019/08/20(火) 14:28

渡辺ひかる様

ご返信ありがとうございます。

VBA自体が難しいと思っていましたが、
ピボットテーブルは特に複雑なのですね。。

自分で調べたり、ご提案いただきました、自動記録を試したりするなかで
該当動作のコード自体は、以下のとおりと推測するに至ったのですが、

1:値フィールドの集計方法を「合計」に変更する
　⇒Function = xlSum

2:値フィールドのセル書式を3桁カンマ表示（数値）に変更する
　⇒NumberFormat = "#,##0"

上記を、すでに動作確認できている既存のマクロにどのように組み込んで
（既存マクロの動作を阻害しない形で、設定対象を定義して）、
セットで機能させるか、
というところをご指導いただきたく、質問した次第です。

そもそも、無理なことを求めた質問になってしまっていたら
申し訳ございません。

よろしくお願い申し上げます。
（Bioferman） 2019/08/20(火) 15:01

 ＞上記を、すでに動作確認できている既存のマクロにどのように組み込んで 
 ＞（既存マクロの動作を阻害しない形で、設定対象を定義して）、 
 ＞セットで機能させるか、 
 ＞というところをご指導いただきたく、質問した次第です。

ピボットテーブルの操作のみのコードは完成しているということでしょうか？

であれば、その　プロシージャ名を　現行のマクロの　適当な部分（おそらく最後）に
書き込んで呼び出すようにすればいいのでは？

 ＞それらしいコードを挿入しても、構文エラーが出てしまいます。

どこでエラーが出るのか書いてもらわないと何とも言えません

 ＞該当動作のコード自体は、以下のとおりと推測するに至ったのですが、

 推測したら確認しないと・・・

（渡辺ひかる） 2019/08/20(火) 17:44

失礼しました。

私が冒頭の投稿で書いたマクロを確認いただきたいのですが、
そこまでは完成しており、
動作も確認しました。

ここに上述の2要件を加えたく、
途中、末尾などさまざまな箇所に、思いつく限りの形で、
マクロ自動記録で確認した上述の構文を追加したものの、
様々なエラーが出る、という、状況です。
エラー内容は複数にわたるため、全て挙げるのは困難ですが、
変数定義が重複している、というものか、
対象を定義できていない、というものが、多かったです。
恐らく、対象の定義の仕方がポイントなのかと思われますが、
その点は、自動記録での確認は困難に思われましたので、
質問した次第です。

マクロの基本的な仕組みを理解できていないため、
簡単にかんがえていたのですが、
習熟された方でもそうそう実現できないレベルの難しい内容、
ということが推察できました。
ありがとうございました

（Bioferman） 2019/08/21(水) 00:20

ピボットのレイアウトがわかりませんので、何とも言えませんが、
こんな感じで出来ませんか。

Sub Sample()

    Dim pvt As PivotTable
    Dim pvf As PivotField

    For Each pvt In ActiveSheet.PivotTables
        With pvt
            .InGridDropZones = True
            .RowAxisLayout xlTabularRow
            .HasAutoFormat = False
            .RepeatAllLabels xlRepeatLabels

            For Each pvf In .PivotFields
                If pvf.Name <> "データ" Then
                    pvf.Subtotals(1) = False
                End If
            Next
            For Each pvf In .DataFields
                pvf.Function = xlSum
                pvf.NumberFormat = "#,##0_ "
            Next
        End With
    Next
End Sub

（きまぐれ） 2019/08/21(水) 07:50

きまぐれ様
お世話になります。

返信ありがとうございます。

ご提示いただいたマクロをテストしましたところ、望んでいた通りの挙動となりました。
本当にありがとうございます。

あと1点だけ、後出しで申し訳ないのですが、値フィールドの列（項目数）が複数の場合に
「実行時エラー　1004 PivotFieldクラスのSubtotals プロパティを設定できません」
というエラーが出てしまいます。
（1項目の場合は正常に動作します。）

値フィールドが複数項目になってもエラーにならないようにするには
どのようなマクロとすればよろしいでしょうか。

ピボットを多用する部門に所属しておりまして、
本マクロが完成しますと、
同僚も喜んでくれそうです。

お願いばかりで恐縮ですが、よろしくお願い申し上げます。
（Bioferman） 2019/08/21(水) 09:39

Excel2010が手元に無いので、想像ですが、
おそらくExcel2010ではデータと言う名前のpivotFieldが無いのでは？
以下に変更して下さい。

    For Each pvf In .PivotFieldsFields
        On Error Resume Next
        pvf.Subtotals(1) = False
        On Error GoTo 0
    Next

（きまぐれ） 2019/08/21(水) 11:33

きまぐれ様

ご返信ありがとうございます。

頂戴した部分を置き換えたところ、
想定どおりに動作しました！
バージョンによっても異なるのですね。
お手元にないなかでも適切にご指導下さり、恐縮です。。

御礼・ご報告までに、
最終的なマクロを、こちらに残させていただきます。

Sub ピボット修正()
'Keyboard Shortcut: Ctrl+Shift+T

    Dim pvt As PivotTable
    Dim pvf As PivotField
    For Each pvt In ActiveSheet.PivotTables
        With pvt
            .InGridDropZones = True '従来のピボットテーブルレイアウトにする
            .RowAxisLayout xlTabularRow '表形式にする
            .HasAutoFormat = False  '「更新時に列幅を自動調整する」を「オフ」
            .RepeatAllLabels xlRepeatLabels '「アイテムのラベルをすべて繰り返す」を「オン」
            For Each pvf In .PivotFields
            On Error Resume Next
            pvf.Subtotals(1) = False
            On Error GoTo 0      '集計を削除する
            Next
                For Each pvf In .DataFields
                pvf.Function = xlSum    'フィールドの集計方法を「合計」に変更する
                pvf.NumberFormat = "#,##0_ "    '値フィールドのセル書式を3桁カンマ表示（数値）に変更する
            Next
        End With
    Next
End Sub
（Bioferman） 2019/08/21(水) 12:10

コメント返信：

[ 一覧(最新更新順) ]

[[20190820131358]] 『(Excel2010 VBA)アクティブセルのピボットの初期吹x（Bioferman） ページの最後に飛ぶ

[[20190820131358]]　『(Excel2010 VBA)アクティブセルのピボットの初期吹x（Bioferman）　ページの最後に飛ぶ