[[20201029142438]]　『VBA　ピボットテーブル自動更新』（山口）　ページの最後に飛ぶ

『VBA　ピボットテーブル自動更新』（山口）

ピボットテーブルの自動更新がうまくいきません。

下記コードにてテストシートのデータが変更になれば、
ピボットテーブルを自動で変更したいのですが、
どのようにすれば良いですか？

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    Worksheets("テスト").PivotTables("テスト").PivotCache.Refresh
    Application.EnableEvents = True

End Sub

＜使用 Excel：unknown、使用 OS：unknown ＞

元データとピボットテーブルは、同じシート（テスト）にあるのでしょうか。

（マナ） 2020/10/29(木) 19:51

元データは別のシートにあります。

初歩的な質問で申し訳ありません。
よろしくお願いします。
（山口） 2020/10/30(金) 06:28

＞元データは別のシートにあります。
そういうことであれば、もしかしてピボットテーブルに"テスト"なんて名前もついてなかったりしませんか？

また、Changeイベントがダメとは言いませんが、ピボットテーブルのあるシートがアクティブになったときに処理すれば十分だったりしませんか？

このほか、使用Excel：unknownとなっているので該当するかわかりませんが、office365でマクロの記録でExcel君にコードを作ってもらってから改造してみると、↓のような命令で更新することができることがわかります。

 Worksheets("別のシート").PivotTables(1).PivotCache.Refresh

よって、まずはご自身もマクロの記録を使って、どのような命令をすればよいのか探ってみてはいかがでしょうか？

（もこな２） 2020/10/30(金) 07:18

ありがとうございます。

データベースが増えたりしたときに、
データベースの範囲の変更などできるのでしょうか？
（山口） 2020/10/31(土) 17:29

データベースのあるシートモジュールに記載します。
データベースの範囲はCurrentRegionで規定しているので、
矩形の範囲であり、周囲すべてが別のデータから分離して（空白行・列で囲まれて）いること。
データベースはテーブルであっても上記条件が必要。
途中に行挿入をした場合はその行にデータを入力するまで、範囲が狭くなります。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Const データベース先頭 = "A1"               '要変更 'データベースの左上隅のセル名
    Const ピボット = "ピボットシート"           '要変更 'ピボットテーブルのあるシート名
    Dim r As Range
    Set r = Range(データベース先頭).CurrentRegion
    If Intersect(Target, r) Is Nothing Then     'データ内orデータ行削除の判定
        If Intersect(Target, r.Offset(1)) Is Nothing Then Exit Sub
    End If
    Application.EnableEvents = False    '自身のシートにpivotがある場合のevents防止
    With Sheets(ピボット)
        .PivotTables(1).SourceData = r.Address(, , , True)
        .PivotTables(1).RefreshTable
    End With
    Application.EnableEvents = True
 End Sub

（kazuo） 2020/10/31(土) 23:05

ミスあります。

  ＞     .PivotTables(1).SourceData = r.Address(, , , True)　は
　       .PivotTables(1).SourceData = r.Address(, , r1c1, True)
他にもバグありボツにして下さい。
（kazuo） 2020/11/01(日) 07:45

 実験してみましたが、

 ピボットテーブルのデータソースの範囲をテーブルにしておけば
 テーブルの行/列の追加・挿入はExcelが勝手に対応してくれるので、
　
 マクロでやらなきゃいけないのは

  Worksheets("Sheet2").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotCache.Refresh

 だけです。

 大事になのは、
  1) データソースのテーブルのあるシートの Changeイベントに書く
  2) ピボットテーブルのあるシート名、ピボットテーブル名を間違いなく書く
 ことです
（´・ω・`） 2020/11/01(日) 10:58

ありがとうございました！

データソースの範囲をテーブルにする、とても勉強になりました。

またよろしくお願いします。
（山口） 2020/11/03(火) 22:02

コメント返信：

[ 一覧(最新更新順) ]

[[20201029142438]] 『VBA ピボットテーブル自動更新』（山口） ページの最後に飛ぶ

[[20201029142438]]　『VBA　ピボットテーブル自動更新』（山口）　ページの最後に飛ぶ