今日を乗り切るExcel研究所

Excel に働かされていませんか

Excel のシート名を取得したい

セルにシート名を挿入する方法、シート名をコピーする方法を検討します。また、シート名をクリップボードにコピー行うマクロを公開します。

f:id:shego:20170503225925j:plain

セルにシート名を設定するには

セルにシート名を表示したいことはよくあると思います。

シートタブからシート名をセルにコピー&ペーストすればいいのですが、それを複数シートでやるのは結構な手間です。

単純にシート名を取得するショートカットや数式に使う関数があってもいいはずなのですが、なぜか Excel には用意されていません。

数式を使う方法をインターネットで検索してみると、CELL()という関数で得られる情報からシート名をなんとか切り出すしかないようです。

シート名を切り出す数式には何種類か考えられるようですが、どれも複雑でとても覚えられるものではありありません。

【シート名取得数式の例】

  • =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
  • =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
  • =REPLACE(CELL("filename", A1),1,FIND("]",CELL("filename", A1)),"")

この数式が何をやっているのかは、インターネット上に詳しい解説がいくつも乗っていますので検索してみてください。

手作業でシート名を取得するには

上記数式をセルにコピー&ペーストするのが最善です。

どこかにメモしておくか、本ページをブックマークするなりしてください。

それで終わるのも何なので、ここはキー操作でシート名をコピペする手順を検討します。

シート名をコピーするには、シートタブを編集モードにします。

シートタブを編集モードにするには、タブをダブルクリックするか、右クリックで「名前の変更」を選択します。

これができるショートカットもあっていいはずなのですが、ちょっと調べた限りでは用意されていないようです。

編集モードにたどり着くアクセスキーはAltHORになります。

【手順例】

  1. シートタブのシート名を編集モードにします
    • キー操作: AltHOR
  2. アクティブなシートのタブが選択モードになります
  3. シート名をコピーします
    • Ctrl + CESC(キー操作でシート名編集モードを抜ける)
  4. 目的のセルを選択し貼り付けます
    • Ctrl + V

1回だけならいいですが、複数シートで繰り返すのは無理があります。

 



シート名をクリップボードにコピーするマクロ

シート名をコピーするマクロ VBA を書いてみます。

下記マクロを実行すると、シート名がテキストとしてクリップボードにコピーされます。

シートを複数選択した状態で実行すると、1行シート名でコピーされます。

コピーしたシート名はそのままセルに貼り付け(ペースト)られます。

ブック内の全シートのシート名を取得するマクロも用意しました。

こちらは、隠しシートのシート名も含まれます。

直接セルにシート名を挿入するような関数やマクロやを作成することもできますが、いったんクリップボードにコピーした方が、いろいろ使い回しが効いて使い勝手がいいようです。

その代りというわけでもないのですが、シート名を取得する数式をクリップボードにコピーするマクロも用意しました。

Option Explicit

' 選択シート(複数可)のシート名をクリップボードにコピーします
Sub シート名のコピー()
    copySheetNames ActiveWindow.SelectedSheets
    ActiveSheet.Select
End Sub

' 全シート名をクリップボードにコピーします
Sub シート名のコピー_全シート()
    copySheetNames ActiveWorkbook.Sheets
End Sub

' シート名をセルに表示する数式をクリップボードにコピーします
Sub シート名のコピー_数式として()
    Const spell = "=REPLACE(CELL(""filename"", A1),1,FIND(""]"",CELL(""filename"", A1)),"""")" 

    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .setText spell
        .PutInClipboard
    End With
End Sub

Private Sub copySheetNames(shList As Sheets)
    Dim sheetNames As String: sheetNames = ""
    
    Dim sh As Object
    For Each sh In shList
        sheetNames = sheetNames & sh.Name & vbCrLf
    Next
    
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .setText sheetNames
        .PutInClipboard
    End With
End Sub

【使い方】

  1. 上記 VBA ソースを標準モジュールにコピー&ペーストします
  2. シート名を取得したいシートを開きます
    • 複数シートのシート名を取得したい場合は、それらを複数シート選択します
  3. 「シート名のコピー」マクロを実行します
    • 現在のブックのすべてのシート名を取得したければ「シート名のコピー_全シート」マクロを実行します
  4. 選択された複数シートのシート名がクリップボードにコピーされます
  5. シート名を表示したいセル、あるいはテキストエディタなどで「貼り付け」ます