EXCEL：セルに表示されているテキストをシート名にしたい

質問

仕事で請求書関係のEXCELを作っています。

会社情報というシートの会社名（A3セルからA16セルまで）が変更されると、
会社別にある請求書など（各社３シートあります）の会社名が変わるようにしてあります。

下記のコードを使って、各シートの会社名が変わるとシート名も変わるというようにしようと思いましたが、
表面上はテキストでも関数セルのため、セルに直接入力をすれば変わりますが、このままではシート名が自動で変わらないですよね。

ちなみに変更したいシート名は、

シート名　　　　　　　　内容
○○会社　　　　　　　⇒日報データ
○○会社請求　　　　　⇒請求書
○○会社請求（鏡）　　⇒請求合計

となり、これが各社分（現在は２０社分）あります。
毎月５社ぐらい会社名が変動しますが、
データを入力する事務員さんはEXCEL初心者なので、
毎回シート名を変更することが難しく、作業を自動で行いたいのです。

私自身も、現在VBAを勉強中のため自分の能力ではこれが精一杯で、
困ってしまいました。

どのようなコードを追加したらいいのか、どなたか教えていただけませんでしょうか？

どうかよろしくお願いします！

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If .Value = "" Then Exit Sub
If .Address(0, 0) = "A1" Then
On Error GoTo ELine
ActiveSheet.Name = .Value
On Error GoTo 0
End If
End With
Exit Sub
ELine:
MsgBox "その名称はシート名になりません", 16
End Sub

回答No.3

pc_knight

会社情報シートのA3～A16セルに登録の会社名の順番どおりにその会社の「○会社、○会社請求、○会社請求（鏡））」という３シートを順次１４（社）分＝４２シート分命名するのであれば、次のコードでいかがでしょう。
但し会社情報シートは一番左のシートとします。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim syamei As Variant
'必要シート数を追加
If Worksheets.Count < 43 Then
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count), Count:=43 - Worksheets.Count
End If
'シート名仮設定
For s = 2 To 43
Worksheets(s).Name = s
Next s
For r = 3 To 16
'シート名変更
Worksheets(r * 3 - 7).Name = Worksheets("会社情報").Cells(r, 1)
Worksheets(r * 3 - 6).Name = Worksheets("会社情報").Cells(r, 1) & "請求"
Worksheets(r * 3 - 5).Name = Worksheets("会社情報").Cells(r, 1) & "請求（鏡）"
Next r
End Sub
（注）各シートのセルA1にシート名を表示させるにはWorksheets(r * 3 - 7).Range(“A1”).value = Worksheets(r * 3 - 7).NameなどがNext rの前に必要です。

回答No.2

zap35

＞各シートのA1セルで、○○会社、○○会社請求、○○会社請求（鏡）と、表示されるようにしてあります
の意味が「会社情報シート」のA3セルにある「○○会社」を「○×会社」に変更したら、関数やセル参照によって自動的にシートのA1セルの値も「○×会社」に変わるというなら会社IDは不要です。

以下のマクロは「会社情報シート」のA3:A16の値が変化したら、各シートのA１に変化後の値（文字列）を含んでいるシートを探して、シート名をA1の値に置き換えるマクロです

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
　If Not Application.Intersect(Target, Range("A3:A16")) Is Nothing Then
　　For Each ws In Worksheets
　　　If InStr(ws.Range("A1").Value, Target.Value) > 0 Then
　　　　ws.Name = ws.Range("A1").Value
　　　End If
　　Next ws
　End If
End Sub

冒頭の意味が「自動的に変わるのではなく、前の会社名を入力してある」という意味なら、やはり会社IDのような一意にシートを識別できるものが必要になります

回答No.1

zap35

こういうことでしょうか。
１）「会社情報シート」の（例えば）A3セルにある「○○会社」を「○×会社」に変更したら、
２）同じブック内にある「○○会社」「○○会社請求」「○○会社請求（鏡）」という３枚のシートの名称を
３）「○×会社」「○×会社請求」「○×会社請求（鏡）」に変更する

単純にはできませんよ。それはWorksheet_Changeイベントでは変更後のTergetの値しか取得できないからです。そのため「どのシートの名前を変更するのか」が分からないのです

でもシートを工夫すればできないことはありません。例えば
「会社情報シート」に「会社ID」を設けて、この会社IDは社名を変更しても変えないようにする。そして各社のシートのどこかのセルに、この「会社ID」を入力しておく。
のような方法が考えられます。こうすればどの会社名のシートを変更すれば良いか分かります。

また別の方法として、会社名が変更されたら「新しく３枚のシートを作成する」方法も考えられますね。いずれにせよ、方法を考え直して再度質問されたらいかがでしょうか

補足

早速ありがとうございます！
説明が下手ですみません。ちょっと補足を追加させて頂きますね。

会社情報シートの会社名は、○○会社、○○会社請求、○○会社請求（鏡）
の各シートのA1セルで、○○会社、○○会社請求、○○会社請求（鏡）と、
表示されるようにしてあります。

再度教えていただきたいのですが、
この場合も会社IDを設定したほうがいいのでしょうか？

もう一つの方法「会社名が変更されたら新しく３枚のシートを作成する」ですが、
テンプレート的なものを用意して、その書式と同じフォーマットのシートの作成はできるのでしょうか？

お時間があるときでかまいませんので、良ければまた教えていただけませんでしょうか？
よろしくお願いします。