ピボットグラフのソースについて

質問

お世話になります

ピボットテーブルをソースに作成したピボットグラフなのですが
これのデータソース(データの範囲)をVBAで変更することはできないのでしょうか？

例えばAテーブルと名付けたピボットテーブルからグラフを作成すると
グラフのデータ範囲は[ブック名]シート名!Aテーブルとなりますが
このAテーブルの部分をBテーブルにVBAで変更したいです

操作上では変更もできずVBAでもそれらしい情報が見つかりませんでした。

よろしくお願いします

回答No.4

end-u · ベストアンサー

>ピボットテーブルがあるシートを含んだ状態で複数シートを
>コピーしようとすると
>「テーブルを含んだ複数シートは移動できません」とエラーになります。

それはピボットテーブルではなくて『テーブル』、ListObjectの事です。
>・シートA1にはテーブルa,aを元にしたピボットテーブルb
この『テーブルa』ですね。
このテーブルaが外部データ取り込みによるQueryTableではない通常のテーブルの場合、
一旦テーブルを解除して（範囲に変換）コピー後に再テーブル化してはどうでしょう。

もしくはグラフを一旦シートA1に移動してコピー後に別シートに移動するとか。
これは前回答の時の
>またはピボットテーブルと同一シート上に置くか。
>別シートにしたい場合でも、マクロでコピーするような仕様ならなんとかなるかな。
マクロが参考になりませんか。

お礼

end-u様

ご回答ありがとうございます。
無事いったんテーブルを解除し、グラフ・データシートをまとめてコピー
その後テーブルを再定義して、ピボットテーブルの参照先を変更

でいけました。
長々と分かりづらい説明ご理解頂き、的確な回答ありがとうございました

回答No.3

end-u

>・シートB2にあるピボットグラフは参照元はシートA1のbのまま
>　シートB1にあるピボットテーブルを参照したが変更できない
・シートB2にあるピボットグラフは参照元はシートA1のbのまま
　シートA2にあるピボットテーブルを参照したいが変更できない

..という事でしょうね。
バージョンによって挙動が違うのかな。
2010の場合は複数シートA1,B1をまとめて選択したままコピーすると
B2のグラフはA2のピボットを参照します。
A2のピボットテーブルはA1のテーブルaを参照したままですけどね。
これはあとから変更可。

2003の場合、別シート上のグラフだと確かに参照が元のままですね。
グラフの場所をシート上ではなくてグラフシートにした場合はうまくいきます。
またはピボットテーブルと同一シート上に置くか。
別シートにしたい場合でも、マクロでコピーするような仕様ならなんとかなるかな。

Dim ws As Worksheet
With Sheets
　　Set ws = .Add(after:=.Item(.Count))
　　.Item("B1").ChartObjects(1).Chart.Location Where:=xlLocationAsObject, Name:="A1"
　　.Item("A1").Copy before:=ws
　　ActiveSheet.ChartObjects(1).Chart.Location Where:=xlLocationAsObject, Name:=ws.Name
　　.Item("A1").ChartObjects(1).Chart.Location Where:=xlLocationAsObject, Name:="B1"
End With

お礼

end-u様

たびたびご回答頂きありがとうございます

>>・シートB2にあるピボットグラフは参照元はシートA1のbのまま
>>　シートB1にあるピボットテーブルを参照したが変更できない
>・シートB2にあるピボットグラフは参照元はシートA1のbのまま
>　シートA2にあるピボットテーブルを参照したいが変更できない

まさにこの通りです、誤字脱字申し訳ありません

2010ならまとめて選択してコピーすれば
コピーしたグラフの参照先はコピーしたピボットテーブルになるので
そのピボットテーブルのデータ参照を変更すればよい
と仰られておりますが
当方ExcelファイルそのものもOfficeも2010ですが
ピボットテーブルがあるシートを含んだ状態で複数シートを
コピーしようとすると
「テーブルを含んだ複数シートは移動できません」とエラーになります。

私も当初まとめてコピーすればいけると思っていたのですが
それができずに１つ１つコピーしたら参照元が・・・といった経緯でした

なにかまとめてコピーする手立てはあるのでしょうか？
手動でもVBAでもできませんでした

回答No.2

end-u

例えば、そのテンプレートBookの構成として、
Sheet1にテーブル領域A
Sheet2にピボットB,C、グラフD,E
という構成なのでしょうか。
それとも
ピボットB,C、グラフD,Eがそれぞれ１シートずつ独立している、という事なのでしょうか。

いずれにしても対応するピボットテーブル＆グラフをシートごとコピーして
ピボットテーブルをグループ別レポートにする..という事では済まない話ですか？
>グラフをコピーして使いまわせない..
ピボットテーブルとグラフを切り離さずに対でコピーすれば使い回せますが
そういう話でもないんでしょうね。

>かなり面倒なので..
ピボットグラフ作成マクロについて。
既にチェック済みかもしれませんが参考まで。
http://pvttbl.blog23.fc2.com/blog-category-9.html

お礼

end-u様

再度ご返答頂きありがとうございます。
説明が下手だったか私が理解不足か、再度説明させて頂きます

・シートA1にはテーブルa,aを元にしたピボットテーブルb
　シートB1にはbを元にしたピボットグラフcがあります。

・シートA1,B1をコピーしてA2,B2にします

・シートB2にあるピボットグラフは参照元はシートA1のbのまま
　シートB1にあるピボットテーブルを参照したが変更できない

といった経緯です

回答No.1

end-u

ピボットグラフはPivotLayoutオブジェクトを介してピボットテーブルと関連付けられているので、
テーブルを別のピボットに変更する事はできないのでしょう。多分。
そんなメソッドは無いみたい。

（代案1）
ピボットグラフではなく通常グラフで良いのなら、
作業エリアとしてセル範囲を経由してグラフを作成する代案も考えられます。
セル範囲に、ピボットテーブル範囲を参照する数式をセットし、
その数式をマクロで変更してセル範囲データを書き換え、グラフの内容を変更する案です。

（代案2）
状況によって２つのグラフを切り替えて表示したい場合は、
２つのピボットテーブル・ピボットグラフを作成しておいて、
ピボットグラフは２コとも同じ位置に。
それぞれのグラフのVisibleプロパティを弄って表示非表示を切り替えます。
もしくは
Sheets("Sheet1").Shapes(1).ZOrder msoBringToFront
のようにZOrderプロパティで最前面表示を切り替え。

（代案3）
参照するピボットテーブルを切り替えるのではなく、ピボットテーブル自体のSourceDataを変更する案です。
Sheets("Sheet2").PivotTables(1).SourceData = "Sheet1!!R1C1:R10C2"
'Sheet2の1番目のPivotTableのSourceDataを"Sheet1!A1:B10"に変更する。
２つのデータ範囲のフィールド名とラベル項目名が同じならスムーズに変更できます。

考えようによっては、この（代案3）がもっとも要望に近くできます。
1)Bテーブルを削除。
2)Aテーブルを元Bテーブルがあった位置に移動
　（ここでグラフは元Bテーブル位置のAテーブルを参照している）
3)元Aテーブルがあった位置にAテーブルをコピー。
4)(2)のAテーブルのSourceDataをBテーブルSourceDataに変更。
..これをVBAで記述すれば良いですが
..そこまで拘らずとも単独のPivotTableのSourceData変更で十分な気がします。

お礼

end-u様

ご回答ありがとうございます。
ご提示された3案ですが私も考えには至ったのですが、今回の事案には対応できなかったため
質問させて頂きました。

内容としては、定義したテーブル領域Aとそれを元にしたピボットテーブルB,C
さらにB,Cを元にしたピボットグラフD,Eがあるのですが
これらはあくまでテンプレートとしての役目で、Accessからあるグループ毎に
グラフ化するために、これらテンプレートをコピーして、テーブル領域AにAccessのデータを貼り付けて、グループ毎にシートをわけてグラフを作成
というのが目的でした

ですのでグラフをコピーして使いまわせない(グラフのデータ範囲を変更できない)
という時点でうまくいきません。
ピボットテーブルのソースを変更するのは分かっていたので
ピボットグラフの作成からVBAでやろうとしたのですが、位置・大きさ・タイトルの設定等
かなり面倒なのでデータの範囲さえ変更できれば・・と思った次第です