エクセル VB を使ってシートをまたいだセルへの自動記入を教えてください

質問

エクセルとマクロ(VB)を使い、こづかい帳や家計簿の様な物を想定して、
「前月シート」の「今月残高」が「翌月シート」の「先月残高」に
自動で記入する方法を教えてください。
現在Sheet1のセルには
　　　　 A 　 　B 　　　　 C 　　　　 D
1　2008年12月
2　　　　　　　 12 　 前月残高 　　 99999
3 　　　　　　　　　 収入 　　 5555
4 　　　　　　　　　 支出 　 　 7777
5 　　　　　　　　　 今月残高 　 97777
ずれるので書き直しますと
A1:2008年12月(=Date(2008,B2,1)
B2:12
C2:"前月残高 "
C3:"収入 "
C4:"支出"
C5:"今月残高"
D2:99999
D3:5555
D4:7777
D5:9777(=D2+D3-D4)
D5は式「=D2+D3-D4」、B2は作業セルでA1に入力されている関数
「=DATE(2008,B2,1)」を表示させ、マクロを実行するごとにSheetが一つずつコピー増加され、B2が+1し、それに伴いA1の年月が変わります。
新しく現れたSheetの名前は、A1を参照して(この場合ですと)「12月」となります。
このマクロを実行するごとに「12月」「1月」・・・とシートが増えていきますが、
(先月の)「今月残高」を(翌月の)「前月残高」に記入する方法が分かりません。

VBAは
Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2008/12/9 ユーザー名 :
'

'
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(2)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = Format(Range("A1").Value, "mm") & "月"
Sheets("Sheet1").Select
Range("B2").Value = Range("B2").Value + 1
Range("D2:D4").Select
Selection.ClearContents
Sheets("sheet1").Select
Range("d2").Select
ActiveCell.FormulaR1C1 = "='12月'!R[3]C"
End Sub

最後の行の 「"='12月'!R[3]C"」をマクロを実行するごとに「 "='1月'!R[3]C"」
「 "='2月'!R[3]C"」・・・・・・・
とする方法は無いでしょうか。よろしくお願いいたします。

回答No.1

n-jun · ベストアンサー

結果として、シートインデックス２（左から２番目）のシート名を数式に入れたいと言う事でしょうか？

Sheets("Sheet1").Range("d2").FormulaR1C1 = "='" & Sheets(2).Name & "'!R[3]C"

例えばこんな感じの事？

お礼

本当にご親切にありがとうございます。

補足

さっそくの回答ありがとうございます。うまく行きました。
Sheets("Sheet1").Copy After:=Sheets(3)の場合
Sheets("Sheet1").Range("d2").FormulaR1C1 = "='" & Sheets(4).Name & "'!R[3]C"
でうまくいきましたが、" & Sheets(X).Name & "は「X枚目のシートを見よ」ということでしょうか。
「&」の意味を教えていただけないでしょうか。

回答No.2

imogasi

先月シートの今月残高の有る行は、変わらないのか、月によりデータの多少で変わるのか、書いてなくて質問にならない。
変わらないならＶＢＡなど使わず、=Sheet1!B20のように関数式でやればすむ。
変わるなら先月シートの今月残高のある行を（ＶＢＡや関数で）探さないといけない。そういうところに目が行ってないようだが、良く考え直してみて質問してください。

補足

>書いてなくて質問にならない。

それはどうも至らない人間で誠に無作法この上なく恐縮至極であります。
かくなりますればあなたのような聖人の如き崇高なるご意見を平に承りたく候。

>変わらないならＶＢＡなど使わず

マクロvbaを使用に至る主たる目的は(vbaの文を読んでいただけば分かる様に)
α実行ごとに月数を増変させる。
βそれに伴いシートの名前を自動記述させる。
γ毎月同じフォームを使うこと。

といったところです。マクロでコピーするわけですからγは書くまでもありませんし、同じフォームを使うのですから残高の行は変わらないのは自明ですね。
先月残高を今月残高に自動記入させるのは、言ってみればもののついでです。
ここで質問するより、30日に１度自分で書き込んでしまえばいいわけですから。

>=Sheet1!B20のように関数式で

シートの名前が毎月変わるのにそう単純に行くのかどうかご教示願います。

>変わるなら今月残高のある行を（ＶＢＡや関数で）探さないといけない。

折角ですから先生のご高説、拝聴仕りたいです。