[[20190608193910]]　『マクロ　ピボットテーブル更新　ファイル名変更』（admin）　ページの最後に飛ぶ

『マクロ　ピボットテーブル更新　ファイル名変更』（admin）

お世話になってます。

マクロ初心者です。
マクロでピボットテーブル更新を組んだブック名を変更したら、
デバッグが出ました。

やりたい事
ファイル名「A」で保存。
ファイル名「B」に変更し、マクロを実行したい。

以下コードです

Sub 更新()

Dim dataS As Worksheet
Dim Pcache As PivotCache
Dim pvt As PivotTable
Dim alWs As Worksheet

Set dataS = ThisWorkbook.Worksheets("実績")

Set Pcache = ActiveWorkbook.PivotCaches.Create( _

    SourceType:=xlDatabase, _
    SourceData:=dataS.Range("D3").CurrentRegion)

For Each alWs In Application.ActiveWorkbook.Worksheets

    For Each pvt In alWs.PivotTables
        pvt.RefreshTable　←ここでデバッグが起こります。
    Next
Next

End Sub

ご教示お願い致します。

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

そもそも何をしたいのでしょうか。
ChangePivotCache メソッド？

こんなのが簡単ではないでしょうか。

 pvt.SourceData =  dataS.Range("D3").CurrentRegion.Address(, , xlR1C1, True)

（マナ） 2019/06/08(土) 20:23

説明が不足していました。
やりたいこと。

1　シート「実績」にあるテーブルデータにデータを追加
　（別シートでテーブルデータから複数のピボットテーブルが既に作成されています）
2　ファイル名「A」を「B」に変えて保存
3　ファイル「B」を開いてデータを更新して上書き

エクセルバージョン365では問題なく動きますが、2016では止まります。
（admin） 2019/06/08(土) 22:46

こういうのでは駄目なのでしょうか？

 Sub 更新()
    Dim r As Range
    Dim ws As Worksheet
    Dim pvt As PivotTable

    Set r = Worksheets("実績").Range("D3").CurrentRegion

    For Each ws In Worksheets
        For Each pvt In ws.PivotTables
            pvt.SourceData = r.Address(, , xlR1C1, True)
        Next
    Next

' ここで名前をつけて保存

 End Sub

（マナ） 2019/06/08(土) 23:21

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

実行したところ、

pvt.SourceData = r.Address(, , xlR1C1, True)でエラーが起こりました。

実行時エラー　1004
そのピボットテーブルのフィールド名は正しくありません。
ピボットテーブルレポートを作成するには、ラベルの付いた列でリストとして編成されたデータを
使用する必要があります。ピボットテーブルのフィールド名を変更する場合は、
フィールドの新しい名前を入力する必要があります。

と出ます。
（admin） 2019/06/09(日) 10:56

実績シートのレイアウトを説明していただけますか

（マナ） 2019/06/09(日) 11:09

これでどうなりますか。

 Set r = Worksheets("実績").Range("D3").CurrentRegion
   ↓
 Set r = Worksheets("実績").Range("D3").ListObject.Range

（マナ） 2019/06/09(日) 11:18

実績シートレイアウト

1　D3からAI3まで店舗cdなどの項目があります
2　D列からN列までO列からAI列を参照した数式が入っています。
3　これをテーブル化しています。

アドバイス頂いたとおりに
Set r = Worksheets("実績").Range("D3").ListObject.Range
に変更したろころ問題なく動きました。

何度もありがとうございました。
1週間もネットで調べていたことがこんなにも早く解決していただいて感激です。

もう一つご教示お願いします。
＞pvt.SourceData = r.Address(, , xlR1C1, True)
これは、ピボットテーブルの基データはシート実績のセルD3のリストという意味でよろしいでしょうか？

（admin） 2019/06/09(日) 12:20

はい。「データソースの変更」で範囲を再設定する操作に相当します。
このほうがよかったです。

 Sub 更新()
    Dim テーブル範囲 As String
    Dim ws As Worksheet
    Dim pvt As PivotTable

    テーブル範囲 = Worksheets("実績").Range("D3").ListObject.Range.Address(, , xlR1C1, True)

    For Each ws In Worksheets
        For Each pvt In ws.PivotTables
            pvt.SourceData = テーブル範囲
        Next
    Next

 End Sub

（マナ） 2019/06/09(日) 12:43

ありがとうございます。

なるほど、よりわかりやすくなりました。

またわからないことがありましたらお願いします。
（admin） 2019/06/09(日) 13:14

でも、せっかくテーブル設定にしてあるなら
データソースをセル範囲ではなくテーブル名にしておくだけで
マクロ不要になるのでは？

（マナ） 2019/06/09(日) 13:22

その通りです。

しかし、データ件数が50万行を超えるため、実務で使用する際に
一度テーブルのシートを削除して使用しているのです。

また、ピボットテーブルが12個あるため一度に更新するマクロが必要でした。

（admin） 2019/06/09(日) 17:45

＞一度テーブルのシートを削除して使用しているのです。

その必要があるのかわかりませんが、それなら仕方ないです。
でも60万行以上って、何をするにしても時間かかりそうですね。

＞また、ピボットテーブルが12個あるため一度に更新するマクロが必要でした。

手作業でも、「すべて更新」できませんか。

（マナ） 2019/06/09(日) 19:29

「すべて更新」後にシート削除すると、
ファイル名を変更したのでダメでした。
（admin） 2019/06/11(火) 18:41

シート削除の必要性が理解できていませんので
必要なら忘れてください。

（マナ） 2019/06/11(火) 19:00

コメント返信：

[ 一覧(最新更新順) ]

[[20190608193910]] 『マクロ ピボットテーブル更新 ファイル名変更』（admin） ページの最後に飛ぶ

[[20190608193910]]　『マクロ　ピボットテーブル更新　ファイル名変更』（admin）　ページの最後に飛ぶ