[excel2007]可変範囲のグラフを含むシートのコピーについて

質問

[excel2007]可変範囲のグラフを含むシートのコピーについて

・やりたいこと
可変範囲を参照するグラフをシート内に作成し、
そのシートを複数コピーして、可変範囲を参照するグラフのまま利用したい。

・現状
以下のサイトを参考に、可変範囲のグラフをシート内に作成しました。
http://www2.odn.ne.jp/excel/waza/graph.html#SEC13

しかしこのシートをコピーすると、
名前"label","value"は新しいシートに複製されますが、
グラフの参照範囲はその名前でなく、固定値(新しいシートのA2:A5など)になってしまいます。
可変範囲のままシートをコピーしたいです。
(excel2003だと、そもそもコピー前のシートを参照するようですが)

・環境
excel2007

可変範囲のグラフの作成方法は、上記サイトの方法に限定はしていません。
何か良い方法をご存知でしたら、ご教授ください。
よろしくお願いいたします。

回答No.2

end-u

>しかしこのシートをコピーすると、
>名前"label","value"は新しいシートに複製されますが、
>グラフの参照範囲はその名前でなく、固定値(新しいシートのA2:A5など)になってしまいます。

2007で確認しました。そうなりますね。仕様のような気がします。
一般操作での回避方法は思いつかなかったので

１）アクティブシートをコピー。
２）コピー後シートのグラフを一旦削除。
３）元シートのグラフを複製してコピー後シートに移動。
４）移動したグラフのSERIES関数のシート名を置換する。

このような手順をマクロ化してみました。
単純なグラフでしか試してませんので、複雑なパターンに対応できるかどうかは不明です。
また、マクロによる対応は避けたいという事であればスルーでお願いします。

Sub test()
　　Dim ws　　As Worksheet　 '元シート用変数
　　Dim cht　 As ChartObject 'グラフLoop用
　　Dim sr　　As Series　　　'系列Loop用
　　Dim sName As String　　　'新シート名用
　　Dim tmp　 As String　　　'SERIES関数文字列用
　　Dim rep　 As String　　　'元シート名用

　　'一応ActiveSheetのグラフ数チェック
　　If ActiveSheet.ChartObjects.Count = 0 Then
　　　　MsgBox "no charts": Exit Sub
　　End If

　　'ActiveSheetを変数にセット、コピー
　　Set ws = ActiveSheet
　　rep = ws.Name
　　ws.Copy after:=ws

　　'コピー後シートのグラフを一旦削除
　　With ActiveSheet
　　　　.ChartObjects.Delete
　　　　sName = .Name
　　End With

　　'元シートのグラフをLoop
　　For Each cht In ws.ChartObjects
　　　　'複製、移動
　　　　cht.Duplicate.Chart.Location Where:=xlLocationAsObject, _
　　　　　　　　　　　　　　　　　　 Name:=sName
　　　　'移動後グラフの系列をLoop
　　　　For Each sr In ActiveChart.SeriesCollection
　　　　　　tmp = sr.Formula
　　　　　　'シート名によってはシングルクォートが付く対策
　　　　　　If InStr(tmp, "'" & rep & "'") > 0 Then
　　　　　　　　rep = "'" & rep & "'"
　　　　　　End If
　　　　　　'置換
　　　　　　tmp = Replace(tmp, rep, "'" & sName & "'")
　　　　　　sr.Formula = tmp
　　　　Next
　　Next

　　Set ws = Nothing
End Sub

お礼

実際に確認までしていただきありがとうございます。

一般操作での回避方法をもう少し探りたいとは思いますが、
作成いただいたマクロも試してみたいと思います。
（職場でないと確認ができないため、まだ試していません。）

回答No.1

HRI-Sankou

絶対位置参照にしてみたらどうでしょうか？
A2:A5 を、$A$2:$A$5 に書き換えてからコピーしてみたら？

補足

回答ありがとうございます。
可変範囲を参照する必要があるので、絶対参照にはできません。

具体的な情報を追加させていただきます。
先に示したサイトで配布されているサンプルファイルを元に記載します。
◆コピー元シート
・グラフ系列値
　　=dynamic.xls!value
・グラフ軸ラベル
　　=dynamic.xls!label
・名前
　label：=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
　value：=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))

◆コピー先シート
・グラフ系列値
　期待
　　='Sheet1 (2)'!value
　実際
　　='Sheet1 (2)'!$B$2:$B$5
・グラフ軸ラベル
　期待
　　='Sheet1 (2)'!label
　実際
　　='Sheet1 (2)'!$A$2:$A$5
・名前(これは期待通り可変範囲を参照するように複製される）
　label：='Sheet1 (2)'!$A$2:INDEX('Sheet1 (2)'!$A:$A,COUNTA('Sheet1 (2)'!$A:$A))
　value：='Sheet1 (2)'!$B$2:INDEX('Sheet1 (2)'!$B:$B,COUNTA('Sheet1 (2)'!$B:$B))