エクセルのVBAでシートを追加更新していきたいのですがうまくいきません

質問

エクセルのVBAでシートを追加更新していきたいのですがうまくいきません…。

すみません、どなたかぜひ教えてください。
エクセルはそこそこ使えますが、マクロ関係はまったくの初心者です。
excel2003で作業しています。

やりたいのは、実行することによって

(1)同一ブックの中に同じ形式のシートを追加する。
(シート名は「0」「1」「2」…というように、ただの数字にしています)

(2)セル「K4」の数式
「=I4+'( 1 )'!K4」を
「=I4+'( 2 )'!K4」
というように、どのシートでも、直前のシートのセル「K4」の数値と更新したシートのセル「I4」の数値の合計を「K4」に表示する。
(以下、「I5」「K5」、「I6」「K6」…も同様にしていきます)

ようにしたいのです。

(2)のために、
Range("k4").Value = "=I4+'(" & Range("j1").Value - 1 & ")'!K4"
（「J1」には、「新しいシート名(数字)と同じ数字」が入るようにしてあります）

としましたが、
「　=I4+'(1)'!K4　」としたいのに、
「　=I4+'[1](1)'!K4　」
と表示され、うまくいきません。

また、実行するたびに、
「値の更新」ということで、ファイルを指定するよう指示が出ます。キャンセルすると「#REF!」になります。

何が原因なのか、どうすればいいのかがさっぱり分かりません。
身の程知らずなレベルに挑戦しているのかもしれませんが、どなたか助けていただければ幸いです。
よろしくお願いします！
(よく分かっていないため、質問の仕方が適当でなかったらすみません…)

回答No.2

keithin · ベストアンサー

＃コメント
シートを追加するブックとマクロが載せてあるブックは別なのか同じなのかが不明です。
このため「どのブックに」一連の操作をしたらいいのか，マクロの中で記述できません。

＃マクロを検討する前提
「前のシート」が存在しないシート名ゼロを作成するため，一連の操作を実施することはできない
基点となるシート名ゼロは，一番最初にマクロを実行するより前に既に作成済みである
なお，全角文字のゼロと半角文字のゼロを間違えてはいけない

サンプルマクロ：
sub macro1()
　dim n as long

　'シートを作成する
　worksheets.add after:=worksheets(worksheets.count)
　on error goto errhandle
　activesheet.name = n
　on error goto 0

　'数式を入れる
　range("K4:K6").formula = "=I4+'" & n - 1 & "'!K4"
　exit sub

errhandle:
　n = n + 1
　resume
end sub

＞何が原因なのか、どうすればいいのかがさっぱり分かりません

J1に入れてあった式が間違ってると考えるのが妥当です。
存在しないシート（間違ったシート名）を参照しろと書かれた数式を投入しようとすると，そんなシートはこのブックに見あたらないけどどこにありますかと「値の更新」が現れ，そして無視ルと有りませんという意味の#REFになります。

お礼

ありがとうございました！！
丁寧に教えていただけて、本当に本当にうれしいです。
とても勉強になりました。

ご指摘の通り、そもそものシート名が間違っていたようです。
ただの数字だったファイル名に( )をつけたところ、きちんと更新してくれました。
(「うっかり」も知識が乏しいと巨大な落とし穴…)

本当に助かりました。
またの機会がありましたら、ぜひ次も教えていただければ幸いです。

回答No.1

ka_na_de

こんばんは。

これでどうですか？

Sub test()
　Range("k4").Formula = "=I4+" & Range("j1").Value - 1 & "!K4"
End Sub

＞(シート名は「0」「1」「2」…というように、ただの数字にしています)

ただの数字では、別の事例で不具合が出た経験があります。
具体的には忘れました。1を1月にして解決した記憶があります。
今回もs0,s1,s2のようにしたほうがいいかも。

お礼

さっそく助けてくださってありがとうございます！

上述の通り変えてみても変わらなかったのですが、
ためしにファイル名に(　)をつけたところ、きちんと更新してくれました。
ただの数字ってダメなんですね…。

勉強になりました。本当にありがとうございました！