[[20160508053202]]　『複数シートにあるピボットテーブルのデータソース』（rion）　ページの最後に飛ぶ

『複数シートにあるピボットテーブルのデータソースを一度に変更したいです。』（rion）

エクセルブックをコピーしてもう１つのブックを作成すると、ピボットテーブルが元のブックのデータソースを参照してしまいます。
※元々のピボットテーブルの参照先は、同一ブック内の別シートです。

根本の質問としては、
1.上記のようにブックをコピーした際にピボットテーブルの参照先がコピー先（同一ブック）になるようにするにはどうすればよいか。
※たまに出来るのですが、どうすると出来るのかがわかりません。

対策の質問として
2.ネットで調べたマクロで

sub test()
Dim s
Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
s = Split(pvt.SourceData, "!")
pvt.SourceData = s(UBound(s))
Next
End Sub

上記で基本的に解決出来ております。しかし、「ActiveSheet」に対する処理のため、ピボットテーブルが複数シートに存在すると、シートを変更して複数回マクロを実行しなければなりません。
あまりマクロは詳しくないので、自分なりに「ActiveSheet」の部分を「Thisworkbook」に変更して実行してみましたが、エラーにはならないのですが、データソースは変更されませんでした。
ブック全てのピボットテーブルのデータソースを変更するには、上記のコードをどのように修正すればよいのでしょうか？

※回答は２のマクロだけでも結構ですので、よろしくお願い致します。

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

 機械的に全シートを対象にしただけで検証はしていません。

 Sub test()
    Dim s
    Dim pvt As PivotTable
    Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
        For Each pvt In sh.PivotTables
            s = Split(pvt.SourceData, "!")
            pvt.SourceData = s(UBound(s))
        Next
    Next

 End Sub

（β） 2016/05/08(日) 06:51

＞※元々のピボットテーブルの参照先は、同一ブック内の別シートです。

 もし、過去ログのわたしの回答を参考にされたのであれば、
 そのマクロは、同一シートが前提の質問なので、エラーがでる確率が高いです。
 （たまたまうまく動作するケースもありますが）

で、元ブックが参照先になる件ですが、ファイルコピーしてからそのブックを開き、
不要なシートを削除すれば問題ないと思います。

（マナ） 2016/05/08(日) 13:26

β様、マナ様ご回答頂き有難うございました！

β様
ご教示頂いた記述で想定した結果を得ることが出来ました！
有難うございました！
※ただ、全部で６シートのブック（そのうち２シートに合計１０個程度のピボットテーブルがあります)で実行すると、１０秒以上かかってしまうことだけちょっとネックです＾＾；
PCスペック
　レッツノートLX4　CPU:corei7 メモリ：8GB

マナ様
以前マナ様が回答されていたものを参考にさせて頂きました。大変お世話になりました！

＞そのマクロは、同一シートが前提の質問なので、エラーがでる確率が高いです。

そうなのですね。私の場合は結果もスピードも最高だったので、あとは複数シートに適用するだけだと
思っていたのですが、今の自分の力では書き換えることが出来ませんでしたorz

＞で、元ブックが参照先になる件ですが、ファイルコピーしてからそのブックを開き、
＞不要なシートを削除すれば問題ないと思います。

すみません、ここが理解できません。
Aブック（コピー元）をファイルコピーしてBブックを作成し、Bブックを開き、
Bブックの不要なシートを削除する。ということでしょうか？

まず、私の求める結果は、AブックはAブック内で参照を完結し、
BブックはBブック内で参照を完結させたいと考えています。

そうすると、不要なシートを削除するということが、
AブックもBブックも出来ない（不要なシートがない）です。

（rion） 2016/05/08(日) 15:17

＞エクセルブックをコピーしてもう１つのブックを作成すると

この手順はどのようにしていますか。
シートをコピーして作成していませんか。

（マナ） 2016/05/08(日) 17:44

 >>１０秒以上かかってしまうことだけちょっとネックです＾＾；

 私自身はピボットに詳しくなく、なのでマクロでそれを扱うということも皆無に近いんです。
 ですから【機械的に】複数シート対応をしただけ。

 ピボット内の要素を書き換えると、単に書き換えだけではなく、そのピボットそのものの再作成が行われるんだと思いますが
 20個のピボットで10秒以上。う～ん・・・。
 1個あたり 0.5秒以上。これが、とんでもなく悪いレベルなのか、まぁ、しょうがない、こんなもんだろうというレベルなのかは
 判断できません。

 マナさんはピボットにも、お詳しいので　上限がいただけるかもしれませんね。

（β） 2016/05/08(日) 17:59

マナ様

＞この手順はどのようにしていますか。
＞シートをコピーして作成していませんか。

シートをコピーして作成しているのではなく、
一度エクセルを閉じてから、フォルダ上でエクセルファイルごとコピペし、
出来上がったBを開くとAファイルのシートを参照してしまうのです。
全く別のピボットテーブルを使ったエクセルファイルでは、このような現象は基本的になかったです。（あったこともあるが、何故かなくなりました。）

β様
十分実用出来るレベルなので、有難うございました。
皆様、相当お詳しいようだったので、どのような結果になったかの報告をさせて頂いた限りです。

数年間、VBAを覚えてみたいと思って本を読んでも挫折していましたが、ようやく本気になれたところです。
おそらく、今後ご質問させて頂くことが多くなると思いますが、どうかよろしくお願い致します。m(_ _)m
（rion） 2016/05/08(日) 18:32

コメント返信：

[ 一覧(最新更新順) ]

[[20160508053202]] 『複数シートにあるピボットテーブルのデータソース』（rion） ページの最後に飛ぶ

[[20160508053202]]　『複数シートにあるピボットテーブルのデータソース』（rion）　ページの最後に飛ぶ