[[20200805112017]]　『VBA　ピボット更新について』（ピノ）　ページの最後に飛ぶ

『VBA　ピボット更新について』（ピノ）

いつもお世話になっております。

以前、本サイトで教えて頂いたピボット更新について質問です。

元表が可変なので、毎回元表範囲を取得し、
ピボット更新を行うという操作をVBAで行いたいです。

以下、ピボット更新の一連の構文です。

この中の、以下?@の一文でピボット更新を行いたいのですが、
更新されない為、?Aの構文を行い、更新操作を2重でしています。

他でこのマクロを使用した際は、うまく更新されたのですが、、、
今回はうまくいきませんでした。

原因がわかれば教えて頂けたら幸いです。

'元表取得後、ピボットデータ更新’?@

        pv.SourceData = sh.Range("B5").CurrentRegion.Address(True, True, xlR1C1, True)

'更新しない場合があるので、再度更新操作’?A　※?@のみで更新する場合は不要

        ActiveSheet.PivotTables("ピボットテーブル5").PivotCache.Refresh

Sub データ加工()

    '▼ピボット更新
        'A2アクティブ領域をコピー
        Workbooks("1_データ.xlsx").Activate

        LR = Cells(Rows.Count, 1).End(xlUp).Row
        Range("A2:I" & LR).Copy  '見出し行以外をコピー

        'フォームに貼付け
        Workbooks("ピボット作成フォーム.xlsx").Worksheets("貼付").Range("A3:I" & LR).PasteSpecial Paste:=xlPasteValues

        'ピボット更新
        Workbooks("ピボット作成フォーム.xlsx").Activate
        Worksheets("サマリ").Select

        Dim pv As PivotTable
        Dim sh As Worksheet

        'ピボットテーブルを変数設定
        Set pv = Workbooks("ピボット作成フォーム.xlsx").Worksheets("サマリ").PivotTables(1)

        '元データシートを変数設定
        Set sh = Workbooks("ピボット作成フォーム.xlsx").Worksheets("貼付")

        '元表取得後、ピボットデータ更新’?@
        pv.SourceData = sh.Range("B5").CurrentRegion.Address(True, True, xlR1C1, True)

        '更新しない場合があるので、再度更新操作’?A　※?@のみで更新する場合は不要
        ActiveSheet.PivotTables("ピボットテーブル5").PivotCache.Refresh

'加工ファイルを保存
End Sub

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

シートを見ないと判らないので、途中で止めて、sh.Range("B5").CurrentRegion.Address(True, True, xlR1C1, True) の内容をイミディエイトウィンドウで表示してみて、それが思ったようなセル範囲になっているか確認してみてください。

CurrentRegionって、セルに隣接している文字列があると、そこも同じ表として解釈するので、表の周囲に文字列入力すると、範囲が思った通りにならない場合があります。 B5セルを指定していても、4行目に何か入力されていると、そこも範囲に入ったり、とか。
（???） 2020/08/05(水) 12:01

???さま
有難うございます！

イミディエイトウィンドウで表示したところ、以下の内容で、意図している範囲が取れているようです。

 [ピボット作成フォーム.xlsx]貼付!R2C1:R28C9

貼付シートに指定範囲以外に文字列は存在しないので、
セルに隣接している文字列も特にないと思われます。

（ピノ） 2020/08/05(水) 13:33

範囲に間違いが無いとすると、よく判りませんね。このコードで確かにテーブル更新されました。
エラーメッセージと、どの行でエラーになるのかを教えてください。

気になる点としては、pv を指定するときは PivotTables(1) を使っているのに、更新するときは PivotTables("ピボットテーブル5") と名称指定していますね？ピボットがシートに1つしかないなら、更新対象も PivotTables(1) で良いのでは？

後半は、なんか無駄な変数代入ばかりに思います。無駄が多いせいで、間違いを見落としているように思いますが、1つしかないピボットテーブル名を間違えるとは思えない…、けど、間違えた？
（???） 2020/08/05(水) 14:18

 かぶりましたが、そのまま
 Set pv = Workbooks("ピボット作成フォーム.xlsx").Worksheets("サマリ").PivotTables(1)
 と
 ActiveSheet.PivotTables("ピボットテーブル5").PivotCache.Refresh
 で、
 pvと、PivotTables("ピボットテーブル5") はホントに同じものですか?

 pv.PivotCache.Refresh
 としてみたらどうなりますか？  
（´・ω・`） 2020/08/05(水) 14:20

???様、（´・ω・`）様

ありがとうございます。
仰る通り,ピボットテーブルはシートに1つしかないので、
両方ともPivotTables(1)いいですね、！！
PivotTables(1)の意味を分かっておりませんでした。
初歩的ですみません。

＞（´・ω・`）様
pv.PivotCache.Refresh
としてみても、更新できました。
PivotTables("ピボットテーブル5")＝PivotTables（1）だからですかね。

いずれにしても、「PivotCache.Refresh」は記載しないと、更新できないんですかね？

以前マクロを作成した際は、
pv.SourceData = sh.Range("B5").CurrentRegion.Address(True, True, xlR1C1, True)
の一文で、元データ取得とデータ更新が出来たのですが…

Dim pv As PivotTable

            Dim sh As Worksheet

            'ピボットテーブルを変数設定
            Set pv = Workbooks("ピボット作成フォーム.xlsx").Worksheets("サマリ").PivotTables(1)

            '元データシートを変数設定
            Set sh = Workbooks("ピボット作成フォーム.xlsx").Worksheets("貼付")

            '元データシートを範囲設定（可変）とし、ピボットデータ更新’?@
            pv.SourceData = sh.Range("A2").CurrentRegion.Address(True, True, xlR1C1, True) '1行目は空白でA2セルからデータがあること

            '更新しない場合があるので、再度更新操作’?A　※?@のみで更新する場合は不要
            ActiveSheet.PivotTables(1).PivotCache.Refresh

（ピノ） 2020/08/06(木) 08:39

↑
すみません。
後半の構文は無視してください！
（ピノ） 2020/08/06(木) 08:39

 >以前マクロを作成した際は、
 >pv.SourceData = sh.Range("B5").CurrentRegion.Address(True, True, xlR1C1, True) 
 >の一文で、元データ取得とデータ更新が出来たのですが…

 私の経験では、それだけで更新できた記憶はありませんし、
 マクロではなくて手作業でやっても、
 範囲を変更したときは、手作業で更新かけてますよ
（´・ω・`） 2020/08/06(木) 09:15

ご返信ありがとうございます！
確かに、手作業でやってもそうですよね…

ということは、以前のマクロがおかしかったということですね…
更新の一文も忘れずに入れるようにいたします。

（ピノ） 2020/08/06(木) 13:59

コメント返信：

[ 一覧(最新更新順) ]

[[20200805112017]] 『VBA ピボット更新について』（ピノ） ページの最後に飛ぶ

[[20200805112017]]　『VBA　ピボット更新について』（ピノ）　ページの最後に飛ぶ