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

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

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

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

このような関数を入れれば、
スクリーンショット 2015 05 29 9 38 49

シート名「売上」が表示されますが、選択しているシートのものしか表示されません。
複数のシートの名前、他のシートの名前を表示させるなら、マクロを使いましょう。
スクリーンショット 2015 05 29 9 39 00

関数については、こちらの記事で書いています。
【関連記事】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を使います。
シート「売上」→「顧客データ」→「日別売上」→・・・と、順番に実行していくしくみです。
スクリーンショット 2015 05 29 9 53 17

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

④ここで実行してみると、こうなります。
スクリーンショット 2015 05 29 9 55 13

シート「売上」→「顧客データ」→「日別売上」→・・・と順番に実行しているのですが、一番右のシート「商品別売上」までセル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から順に、シート名が入力されます。
シートがいくら増えても手間は同じです。
スクリーンショット 2015 05 29 9 59 03

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

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

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

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





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

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

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

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




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』