エクセルで新しく作られた後のシート間でセル値参照の関数を教えてください。

エクセル2003です。次の条件になる関数を教えてください。
知りたいことは、【ひな型】シートのB44に入れる関数です。
現在作成中のブックでは最初のシートが【目次】シート、２番目が【ひな型】シートとなっていて、次のVBAでsheet3から【ひな型】を任意数、生成します。

Sub ひな型コピー()
Set 処理範囲 = Selection.Worksheet.Range(Selection.Address)
For Each 処理セル In 処理範囲
On Error Resume Next
Sheets("ひな型").Copy after:=Sheets(Worksheets.Count)
ActiveSheet.Name = 処理セル.Value
Next 処理セル
End Sub

やりたいことは、新しく作られた２つのシート（sheet3とsheet4)でのセル値参照です。sheet3のB44セルにsheet4のB12を参照したいわけです。ただ、たくさん作られたシート間で参照するのは大変なので、【ひな型】シートのB44セルに最初から入れておけば、というのが願いです。もちろんオートフィルドラッグしていきたいので、１つのセル参照ではありません。http://oshiete.goo.ne.jp/qa/5535868.html にあった関数が一番近いですが後シートのセル参照なのでできません。おわかりになる方、どうぞよろしくお願いします。

No.2 ベストアンサー 回答者： googoo900 回答日時： 2016/01/10 12:36

No.1です。

修正しました。
原因はセルに数式を入れる指示にクォーテーションマークが足りていなかったためです。
自分は英数字のシート名で検証していたため問題なかったのですが、日本語(スペース入り)だとダメなようです。
数式がはいるB12セルが黄色になるようにしています。確認後、不要なら削除ください。(該当行はわかるようにしています)

Sub ひな型コピー()

'追加開始
Dim LastSheetName, ActiveSheetName As Variant
Dim FirstFlag As Integer
FirstFlag = 1
'追加終了

Range("i7").Select
Range(Selection, Selection.End(xlDown)).Select
Set 処理範囲 = Selection.Worksheet.Range(Selection.Address)
For Each 処理セル In 処理範囲
On Error Resume Next
Sheets("ひな型").Copy after:=Sheets(Worksheets.Count)
ActiveSheet.Name = 処理セル.Value

'追加開始
ActiveSheetName = ActiveSheet.Name
If FirstFlag = 0 Then
Sheets(LastSheetName).Range("B12").Formula = "='" & ActiveSheet.Name & "'!B44"
Sheets(LastSheetName).Range("B12").Interior.Color = 65535 'この行は不要なら削除
End If
FirstFlag = 0
LastSheetName = ActiveSheet.Name
'追加終了

Next 処理セル
End Sub

この回答へのお礼

はい、思った通りの結果になりました。ありがとうございました。フラッグの使い方を覚えたいと思います。感謝です。
なお、大したことではありませんが、
Sheets(LastSheetName).Range("B12").Formula = "='" & ActiveSheet.Name & "'!B44"は
Sheets(LastSheetName).Range("B44").Formula = "='" & ActiveSheet.Name & "'!B12"に直しました。

お礼日時：2016/01/10 15:27

No.1 回答者： googoo900 回答日時： 2016/01/09 21:28

質問文のサンプルをベースに、追加しました。

追加した部分がわかるようにしています。
確認ですが、sheet3のB44がsheet4のB12を参照するのですよね？（そのように作っています）


Sub ひな型コピー()

'追加開始
Dim LastSheetName As String
Dim FirstFlag As Integer
FirstFlag = 1
'追加終了

Set 処理範囲 = Selection.Worksheet.Range(Selection.Address)
For Each 処理セル In 処理範囲
On Error Resume Next
Sheets("ひな型").Copy after:=Sheets(Worksheets.Count)
ActiveSheet.Name = 処理セル.Value

'追加開始
If FirstFlag = 0 Then
Sheets(LastSheetName).Range("B44") = "=" & ActiveSheet.Name & "!B12"
End If
FirstFlag = 0
LastSheetName = ActiveSheet.Name
'追加終了

Next 処理セル
End Sub

この回答へのお礼

早速ありがとうございます。
ただ、変化なしです。言葉足らずで私の質問の仕方が悪かったかもしれません。
Sub ひな型コピー()の下には次の２行がありました。
Range("i7").Select
Range(Selection, Selection.End(xlDown)).Select
これによりi7セルの下に例えば１０個の文字列があったとして、ひな型はひな型シートの後に１０個コピーされて、シート名はi7セルの下の１０個の文字列になります。（vbaの通りです。当たり前ですよね）

で、やりたいことはシート３以降において、sheet3のB44セルにsheet4のB12が、sheet4のB44セルにsheet5のB12が、sheet5のB44セルにsheet6のB12が・・・、とシートが複数生成されても、前のシートに後のシートのセル値が表示されてほしいわけです。（実は、後で自分で編集しようと思っていまして、できれば前のシートのb44:g63に後のシートのb12:g31のセル値を参照したいのですが。）もう少しおつきあいいただいて、教えていただければ幸いです。

お礼日時：2016/01/09 22:08