シート削除して同名シート追加するも#REFになる

製品データを週１回まとめているのですが、データ量が多いため
その都度、製品データのシートを別ファイル（データまとめ）に追加しています。
シートの追加をする際、古いデータシートは削除してから追加します。
シートの追加方法は、マクロで行っています。
 
Private Sub CommandButton2_Click()
Workbooks("製品データ.xls").Worksheets("データ").Copy _
After:=Workbooks("データまとめ.xls").Worksheets("まとめ")
End Sub
 
 
別ファイル（データまとめ）のまとめシートのB2セルには以下の数式が入っています。
INDIRECT関数を使用していますが、シートの削除（製品データ!）を行うと、#REFとなります。
 
 
=IF(ISERROR(VLOOKUP($A2,INDIREDCT(製品データ!$B$2:$G$93),4,0)),"データなし",VLOOKUP($A2,INDIRECT(製品データ!$B$2:$G$93),4,0))
 
 
数式に何か落ち度があるのでしょうか？
シートの削除・追加をしても、数式がエラーにならない方法を探していますが
#REFの置き換えをする方法しかないのでしょうか？
 
その他の方法で解決策がありましたら、何卒ご教示お願いします。

No.3 ベストアンサー 回答者： chie65535 回答日時： 2012/06/25 12:46

＞シートの削除・追加をしても、数式がエラーにならない方法

INDIRECT()関数の最初の引数に「文字列」を指定して参照すればよいです。

× INDIREDCT(製品データ!$B$2:$G$93)
○ INDIREDCT("製品データ!B2:G93")

---

質問者さんの式の

INDIREDCT(製品データ!$B$2:$G$93)

のように書くと、以下と同じ意味になります。

製品データ!$B$2:$G$93

つまり「INDIRECT()関数が無いのと同じ」です。

INDIRECT()関数無しに「直接に参照している」のですから、参照シートが削除されれば「#REF」になるのが当たりまえです。

この回答へのお礼

回答ありがとうございます。
なるほど、絶対参照が邪魔をしてしまってるわけですね？
ただ、下の行にコピーするため、絶対参照をつけているのですが
ご回答いただいた式にすると、一つ一つ式を入力するしかないのでしょうか？
なにぶん行数が多いもので(＞人＜;)

お礼日時：2012/06/25 15:48

No.2 回答者： dakedakepuru 回答日時： 2012/06/25 12:37

そうですねー。

一度削除することによって、いくらシート名を同名にしてもうまく噛み合わないのだと思います。

＃REFを新しくつけたシート名で置き換えたほうが無難だと思いますよ。

この回答へのお礼

回答ありがとうございます。
置き換えが一番楽で、無難なんでしょうかねぇ。
ただ、色んな人が利用するのでなるべく工数が少ないものをと思っているので、この方法もまた最終手段として候補にしておきます(^-^)/

お礼日時：2012/06/25 15:44

No.1 回答者： Saturn5 回答日時： 2012/06/25 12:30

数式の中では「シート名」を使ってシートを識別しますが、

内部計算ではオブジェクト番号で識別されています。
ですから、あるシートを削除してから同一のシート名のシートを追加しても、
数式内ののオブジェクト番号は更新されません。

その数式をどいこかにコピーして、数式を消してからもう一度貼り付けてみては
どうでしょうか？
あらたな数式な対しては新しいオブジェクト番号のシートが参照されます。

この回答へのお礼

回答ありがとうございます。
なるほど、数式を別で準備してもってくる方法がありますね。
最終手段としてその方法をやってみようと思います(^-^)/

お礼日時：2012/06/25 15:40