エクセルVBA 別シートの名称を置換に利用したい

質問

皆さんこんにちは。VBA素人のモノです。
※エクセル2013使用しております。

表題の通り、別シートの名称を置換に利用したいのですが
うまく組めないのでご教授ください。

シートには「4月」というシートと
「関数」というシートがあり、「関数」シートには「4月」を参照する関数が
山盛りに入っています。

月が替わった時にボタンを押すと
「4月」シートをコピーし、「5月」シートを作成→
「4月」シート内に入っている関数を全て値にする・・
という所までマクロを組みました。

次に行いたいのが「関数」シート内にある関数の参照先を
'4月'!から'5月'!にしたいのです。

単に4月から5月に置換するだけなら簡単だったのですが
これが毎月行われるとなると難しくて挫折してしまいました。

自分の頭の中では、参照先のシートを
「関数」シートの
「2つ左のシート」→「1つ左のシート」にして
関数シート内を文字を置換をすれば良いのかなぁ？という
ぼんやりとしたイメージがあるのですが
こんなマクロをどうやって組めば良いのか
さっぱり見当がつきません。

こんな拙い説明で申し訳ないのですが
上記のイメージをマクロに出来るのであれば
教えていただけると幸いです。

回答No.2

real beatin（@realbeatin） · ベストアンサー

こんにちは。お邪魔します。

> 「関数」シートの
> 「2つ左のシート」→「1つ左のシート」にして
> 関数シート内を文字を置換をすれば良いのかなぁ？

ＶＢＡで対応するということなら、それでいいと思いますが、
間違いがあるといけないので、対話型にしてみました。
※「関数」シートのシート名をマクロの先頭行で正確に指定してください。

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Sub Re8914124()
Const SF = "関数"
Dim Target As Range
Dim sPrev As String
Dim sNext As String
　　With Sheets(SF)
　　　　If ActiveSheet.Name <> SF Then .Select
　　　　With .Previous
　　　　　　sNext = "'" & .Name & "'!"
　　　　　　sPrev = "'" & .Previous.Name & "'!"
　　　　End With
　　　　Set Target = .UsedRange.Find(What:=sPrev, LookIn:=xlFormulas, LookAt:=xlPart)
　　　　If Target Is Nothing Then
　　　　　　MsgBox "シート" & SF & "　に設定中の数式について" & vbLf & _
　　　　　　　　vbTab & "ふたつ左のシートへの参照： " & sPrev & "　を" & vbLf & _
　　　　　　　　vbTab & "ひとつ左のシートへの参照： " & sNext & "　に" & vbLf & _
　　　　　　　　"置換するマクロを実行しようとしましたが" & vbLf & _
　　　　　　　　"シート" & SF & "　には【" & sPrev & "】を参照する数式がありません" _
　　　　　　　　, vbOKOnly Or vbExclamation, "シート" & SF & "　の月次更新"
　　　　　　Exit Sub
　　　　End If
　　　　If MsgBox("シート" & SF & "　に設定中の数式について" & vbLf & _
　　　　　　vbTab & "ふたつ左のシートへの参照： " & sPrev & "　を" & vbLf & _
　　　　　　vbTab & "ひとつ左のシートへの参照： " & sNext & "　に" & vbLf & _
　　　　　　"置換しますか？", vbOKCancel, "シート" & SF & "　の月次更新") _
　　　　　　= vbCancel Then
　　　　　　MsgBox "キャンセルされました" _
　　　　　　　　, vbOKOnly Or vbInformation, "シート" & SF & "　の月次更新"
　　　　　　Exit Sub
　　　　End If
　　　　.UsedRange.Replace What:=sPrev, Replacement:=sNext
　　　　MsgBox "シート" & SF & "　に設定中の数式について" & vbLf & _
　　　　　　vbTab & "ふたつ左のシートへの参照： " & sPrev & "　を" & vbLf & _
　　　　　　vbTab & "ひとつ左のシートへの参照： " & sNext & "　に" & vbLf & _
　　　　　　"置換しました", vbOKOnly Or vbInformation, "シート" & SF & "　の月次更新"
　　End With
End Sub

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

ＶＢＡを使わない方法もいくつかあるにはありますが、
大抵は、現状の関数を活かす方向で考えた方が、
ブックの機能としては有利になる場合が多いだろうと思いますので、
今回のように、数式の置換という手段で良い、と私は考えています。
＃１様の方法であれば、デメリットは少ないですが、
名前の定義を利用することが、「簡単で」「解り易い」管理方法
になると感じるかどうかは、人それぞれ、と思います。
ご自身が理解できて、メンテにあたる人達のスキルに期待が持てるか、
すべてご自身で管理する、ということでしたら、
名前の定義でもいいと思います。

以上です。

お礼

realbeatin さん、ご回答ありがとうございました。
現状の関数を活かす方法を教えていただき
大変助かりました。
自分がやりたかった事がこんなに長いコードだったとは・・！
もっとお勉強しなくては、という気になりました。
今はrealbeatin さんのコードを読解するのに必死ですが
いつか頭にポンっと浮かぶようになれたらなぁ☆
頑張ります！！

回答No.4

imogasi

簡単な例でやってみた。参考にして、コードを作ってみてください。
下記をまずやってみて、要領をつかんでください。
出てくるシートは４月シートと、データ配置や式のパターンは同じで4月シートのデータ中身が変わったシートが５月シートです。
式で加工しているシートは、４月ーー＞Ｓｈｅｅｔ１、５月のデータを使うシートはＳｈｅｅｔ２としています。
ーーー
Sheet1
B1セルに式　＝’４月’！Ａ２
B2セルに式　＝sum(’４月’！A５：A10)
＜説明サンプルのため、少数の２セルで説明します）
セルの値の結果は、Sheet1のB1は３４、B2は２０
ーー
シート名「４月」シートのデータは、Ａ２に３４、Ａ５：Ａ１０には２，１，３，２、１、１１が元データとして入っていますので。
ーーー
Ｓｈｅｅｔ１をシートコピーして、出来たシートの名前をＳｈｅｅｔ２にする。
下記の、マクロの記録で私が作ったＶＢＡコードを実行する。
Ｓｈｅｅｔ２を開いておく。
ーーー
Sub Macro1()
'
' Macro1 Macro
'
ActiveWindow.DisplayFormulas = True
Range("B1:B2").Select

Sheets("Sheet２").Range("Ｂ1:B2").Select
Cells.Replace What:="４月", Replacement:="５月", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
ーーーー
実行した後の結果（セルの値）は、Ｓｈｅｅｔ２のＢ１は１４、Ｂ２は１１となっています。
「５月」シートのＡ２は１４、Ａ５：Ａ１０の値は１，２，１，３，２，２となっているからです。
またセルに数式を表示して納得してください。
ーー
要点は、数式を表示して、文字列置換（４月ーー＞５月）をしているだけです。

お礼

imogasi さん
私の拙い説明を理解していただき感謝です。
実践してみて納得しました。なるほどー！です。
ネットや本ではこのコードを使う！という所までは理解出来たのですが
実際コードを作るとなると難しくエラーの連発でした。
コードの書き方、勉強になりました。
ありがとうございます。

回答No.3

real beatin（@realbeatin）

＃２です。

表示するメッセージについて訂正があります。
提示した記述に対して[すべて置換]して対応をお願いします。
誤）
　"シート" & SF & "
正）
　"シート【" & SF & "】

失礼しました。

回答No.1

eden3616

関数で使用する参照範囲は名前の定義で一通り作成しておいて
シートコピー時には定義を更新するようにすればいいのでは？
そうすれば、数式は変更せずに、定義だけ変更することで全数式に適応できると思いますが。

VBAによる名前定義の方法は以下を参照ください。
http://www.moug.net/tech/exvba/0050107.html

お礼

eden3616 さん
久しぶりに耳にした「名前の定義」に
なるほどな、と思いました！
次にVBAを駆使する時はこの方法を取り入れたいと思います。
大変勉強になりました、ありがとうございます。