今回もピボットテーブルで是非設定しておきたい項目について解説します。
ピボットテーブルのデフォルト状態では、更新時に列幅が自動調整されるようになっています。せっかく調整していた列幅が、自動調整時に崩れてしまいます。そうならないように、自動調整されない設定にしたいのでその方法を解説します。
この部分も毎回手動で設定している部分です。これもまた手間がかかっているところなので解消できるように勉強しましょう(^^)/
これ気になっている部分でした(^^; 今回もよろしくお願いします！
【この記事でわかることは】
・ピボットテーブル更新時に列幅を自動調整しないようにする方法がわかります
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
前回記事は、ピボットテーブルオプションの設定をVBAマクロで行う方法と「従来のピボットテーブルレイアウトを使用する」に設定する方法などについての解説記事でした。よければ覗いてみてね(^^)/
ボピボットテーブル更新時に列幅を自動調整させない方法
最初に一例として、ピボットテーブルを更新した時の動作画像を見てください。
更新と同時に、列幅も自動調整されたために「縮小表示」に設定していたのにそれを無視して、幅が伸びてしまいました(-_-;)これが許せません！
これでまた表示を調整しなくてはいけなくなってしまいました！
こうならないように、いつも手動で［ピボットテーブルオプション］ダイアログの［更新時に列幅を自動調整する］チェックボックスを外して「自動調整」しないように設定を変更しています。
この［更新時に列幅を自動調整する］チェックボックスを、VBAで操作できれば列幅を自動調整しないようにすることができるので調整してみます。
「マクロの記録」を確認します
実は「マクロの記録」は前回記事「Excel VBA ピボットテーブルを操作する【オプション設定】」で実行して記録していますのでそのまま使っていきます。
この部分です。
・レイアウトと書式
更新時に列幅を自動調整する
ActiveSheet.PivotTables(“ピボットテーブル1”).HasAutoFormat = False
この、PivotTableオブジェクトのHasAutoFormatプロパティをFalseにすれば、「更新時に列幅を自動調整する」チェックボックスのチェックが外れ、自動調整しなくなります。
更新時に列幅を自動調整しないようにするコード
HasAutoFormatプロパティを変更するマクロ
'更新時に列幅を自動調整しないようにする
Sub setHasAutoFormat()
ActiveSheet.PivotTables(1).HasAutoFormat = False
End SubたったこれだけでOKです。
他の設定と同時に指定する場合（あくまで一例です）
他の設定と同時に指定する場合は次のようにします。
'他の設定と同時に「更新時に列幅を自動調整しない」ようにする
Sub setpivotFormatSample()
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(1)
With pvt
.HasAutoFormat = False '列幅を自動更新しない
.InGridDropZones = True 'グリッド内でフィールドのドラッグを可能にするプロパティ
.RowAxisLayout xlTabularRow '表形式にするメソッド
.ColumnGrand = False '列の総計を削除
.RowGrand = False '行の総計を削除
End With
End Sub全てのピボットテーブルに適用する方法
全てのピボットテーブルに対して「更新時に列幅を自動調整しない」ようにするならこんな感じです。
'すべてに対して「更新時に列幅を自動調整しない」ようにする
Sub setHasAutoFormatAll()
Dim ws As Worksheet
Dim pvt As PivotTable
For Each ws In Worksheets
For Each pvt In ws.PivotTables
pvt.HasAutoFormat = False
Next pvt
Next ws
End Sub
・PivotTable.HasAutoFormatプロパティをFalseにする処理をループで繰り返す方法です。
・5行目～、外側の For Each ws In Worksheets ～ Next は、アクティブなブックの全ワークシートWorksheetsコレクションに対するループ処理です。
・6行目～、内側の For Each pvt In ws.PivotTables ～ Next は、各シート上の全ピボットテーブルPivotTablesコレクションに対するループ処理です。
・必要に応じて内側だけ（シート対象）のループに設定するなどして流用できます。
まとめ（おわりに）
・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)
まとめと感想など
どうですか。これで「ピボットテーブル」での苛立ちがだいぶ解消できたんじゃないでしょうか。他にも気になる部分があったら今までのコードに追加設定して行けばよいでしょう(^^)
ここまでに勉強したことが全部参考になっているのですんなり理解できました！もっといろいろ試して見たいと思います(^^)
次回は、ピボットテーブルの書式設定部分をもう少しだけ勉強したいと思います。楽しみにしていてね(^^)/
★★★ ランキング参加中！ クリックしてね(^^)/ ★★★
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/
記事のサンプルファイルをダウンロードできます
今回の記事のサンプルをダウンロードできるようリンク先に登録しています！
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください
【今回分かったことは】
・ピボットテーブル更新時に列幅を自動調整しないようにする方法がわかりました