すべてのシートの名前をセルに入力し、一覧を作るマクロ【Excelマクロ入門】

「シート名　表示」「シート名　セル」「シート名　マクロ」といった検索で当ブログを訪れていただいております。
シート名をセルに入力するには、マクロを使った方が楽です。

シート名を関数で表示させる方法

選択しているシート名を表示させる方法として、関数を使うものがあります。

このような関数を入れれば、
=MID(CELL(“FILENAME”),FIND(“]”,CELL(“FILENAME”))+1,31)

シート名「売上」が表示されますが、選択しているシートのものしか表示されません。
複数のシートの名前、他のシートの名前を表示させるなら、マクロを使いましょう。

関数については、こちらの記事で書いています。
【関連記事】Excelのシート名をセルに表示させる方法・しくみ・注意点 | EX-IT
リンク

すべてのシートのシート名を入力するマクロ

選択しているシート名は、

Worksheets("売上").Name

です。

これを「セルA1」に入れるなら、「セルA1の値は、シート「売上」のシート名である」という表現にしますので、こうなります。

    Range("a1").Value = Worksheets("売上").Name

これを踏まえて、すべてのシートの名前を入力するには、次のようなマクロを書きます。

Sub sheetname()

'■データの入力位置
    Dim n
    n = 1
    
'■すべてのシートの名前を取得し、A列に入力
    Dim w
    For Each w In Worksheets
        Range("a" & n).Value = w.Name
        n = n + 1
    Next

End Sub

すべてのシートで処理を実行するのは、この部分です。

    Dim w
    For Each w In Worksheets

    Next

シート名マクロの書き方

順を追って、このマクロを書いていきましょう。

①　まずは、シート「売上」の名前をセルA1に入れます。

Sub sheetname()
    Range("a1").Value = Worksheets("売上").Name
End Sub

②すべてのシートで繰り返すマクロを書きます。
シートはwという変数を使いました。

    Dim w
    For Each w In Worksheets
    Next

    Range("a1").Value = Worksheets("売上").Name

③シート名を入力する部分（①）を繰り返しの中に入れます。
シート「売上」だけではなく、すべてのシートの名前について、繰り返すので、変数のwを使います。
シート「売上」→「顧客データ」→「日別売上」→・・・と、順番に実行していくしくみです。

    Dim w
    For Each w In Worksheets
	    Range("a1").Value = Worksheets("売上").Name
    Next

④ここで実行してみると、こうなります。

シート「売上」→「顧客データ」→「日別売上」→・・・と順番に実行しているのですが、一番右のシート「商品別売上」までセルA1に上書きされていることが原因です。

シート「売上」は、セルA1、シート「顧客データ」はセルA2・・・とセルの入力位置も変えなければいけません。

A列で、1つずつ、行を増やしていけばいいので、こういうときは、
n=n+1
という表現を使います。

nは変数ですので、
Dim n
で定義し（定義しないと、マクロに伝わらないためエラーになります）、
n=1
と初期値を設定します。
初期値（最初に入る値）は設定しないと、0が入るからです。
セルA0というのは存在しないのでエラーとなります。

⑤結果的に、このようなマクロにすれば、

Sub sheetname()

'■データの入力位置
    Dim n
    n = 1
    
'■すべてのシートの名前を取得し、A列に入力
    Dim w
    For Each w In Worksheets
        Range("a" & n).Value = w.Name
        n = n + 1
    Next

End Sub

セルA1から順に、シート名が入力されます。
シートがいくら増えても手間は同じです。

シート名をセルに表示できると、いろんな使い方ができますので、ぜひ試してみていただければ。
マクロを使えようになると、格段に効率が上がります。
サンプルはこちらです。

EX-ITサンプル　シート名マクロ

マクロについてはこちらの記事も参考にしていただければと思います。
【関連記事】Excelでマクロを書く下準備ーExcelを持っていれば無料で使えますー | EX-IT
リンク

シートへのリンクをつけて、シート名をクリックするとシートを切り替えられるようにするには、こちらの記事を参考にしてみてください。
【関連記事】Excelで目次（シートへのリンク）を作るマクロ | EX-IT
リンク

---

---

---

---

【編集後記】
6月のブログセミナーの募集開始しました。
毎日又は平日毎日更新を目指す方限定です。

https://www.ex-it-blog.com/wordpressseminar/

【昨日の1日1新】
※詳細は→「1日1新」

シャア専用ザク（ORIGIN）作成
クーリエジャポンのセミナー参加