シート削除して同名シート追加するも#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

chie65536（@chie65535） · ベストアンサー

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

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

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

---

質問者さんの式の

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

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

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

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

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

お礼

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

回答No.2

dakedakepuru

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

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

お礼

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

回答No.1

Saturn5

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

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

お礼

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