ワークシート名を変更されても大丈夫

ワークシート関数に CELL() というのがあります。マクロで使う Cells とは全く違っていて、「セルの書式、位置、または内容に関する情報を返す」ものだそうです。
そのセルの絶対アドレスを示す文字列からセルに設定されている書式など、さまざまな機能をもっています。必要がなくて見過ごしていましたが、なかで CELL("filename",○〇) というのに気が付きました。
これは、○〇で示されたセルの属するファイルとワークシート名を "パス名\[ファイル名]ワークシート名" という文字列で返してくれます。その "]" 以降の文字列を取得すれば、ワークシート名が得られるのです。たとえば、次のような例。

ここで A1 はそのワークシート上のセルを指定すればよいだけなので、B2 であろうが X99 であろうが、任意です。

ワークシートの式の例

これで、Range("SheetName") を参照すれば、どこからでもその値が得られます。
セルの値は、こうなっています。

ワークシートのセルの値

しかも、おどろくことに、ワークシートのオブジェクトと名前が動的に紐づいていて、ここにある "originalName" というワークシートの名前を下のタブから例えば "JumpTable" に変えると、この  [SheetName] のセルの内容は "JumpTable" に変わります。しかも、$C$2 に入れた式の引数までがいつの間にか CELL("filename",JumpTable!A1) に変わっているのです！　
つまり、Book のどこからでも、[SheetName] を参照すれば、どんなに名前が変更されていても、もともとの思ったとおりのワークシートにたどり着ける、ということになります。このことは重要です。単にそのセルの属しているワークシート名がほしいのであれば、sheetName = Selection.Parent.Name で簡単に取得できるのですが、名前が変わってしまっては立ち往生してしまいます。

そこで、本題。
ワークシート名は、基本的にはユーザーに変更してもらいたくないものですから、「変更禁止」のコメントをつけるなどして Book を配布します。しかし、場合によると、変更を許容せざるをえないケースが、ひょっとしたらあるかもしれません。そういう場合に、CELL("filename",〇〇) は威力を発揮します。

そのあたりを念頭に、ワークシート名指定の３通りの進化過程を以下に整理しました。所定のワークシートをアクティブにして表示する、という例です。

もっとも簡単な指定

標準モジュール

Private Const newSheetName = "originalName"
Public Sub ActivateTarget()
    ThisWorkbook.Worksheets(newSheetName).Activate
End Sub

簡単ですね。目的のワークシートの名前が絶対に変更されない保証があれば、これで大丈夫。

変更されても安心な指定

標準モジュール

Private Property Get newSheetName()
    newSheetName = Range("SheetName").value
End Property
Public Sub ActivateTarget()
    ThisWorkbook.Worksheets(newSheetName).Activate
End Sub

事前に、最初に示したような式をどこかのワークシート上に用意しておく必要がありますから、少し手間がかかります。しかし、目的のワークシート名をどんなに変更しても大丈夫。
用意した式を含むワークシートは、普段は見えなくてよいので、非表示にしておくとよいでしょう。

変更されても安心な指定     複数のワークシート

ワークシートをひとつだけ扱う、ということは普通ありませんね。複数のワークシートのうち、特定のどれかに代入したり、参照したり、表示したり、といった操作を行うのに、上のマクロを拡張してみました。上と同じように、どこかのワークシート上に一覧表を作成しておく必要があります。その表は、こんな感じ。

"originalName" というワークシートは、すでに "JumpTable" という名前に変更されています。
A1とかA16とか、一定しませんが、これは任意ですので、コピペした時のをそのまま残したせいです。

ワークシートの一覧表に記載する式

標準モジュール

Private Property Get newSheetName(originalName)
      newSheetName = WorksheetFunction.VLookup(originalName, Range("SheetNameTable"), 3, False)
End Property
Public Sub ActivateTarget(originalName)
      ThisWorkbook.Worksheets(newSheetName(originalName)).Activate
End Sub

ActivateTarget "Const" と呼べば、そのワークシートがたとえ "Constants" に変更されていても、全く気にしないで思った通りのワークシートがアクティベイトされます。
上の一覧表の例でいえば、ActivateTarget "originalName" で、名前が変更された  "JumpTable" のワークシートがアクティベイトされます。