同一ブック内のシート名をセルに書き出す

概要

動作確認環境
OS : Win7
Excel : 2010

同一ブック内のシート名をセルA1,A2,A3・・・とA列に書き出します。
非表示状態のシートも対象になります。マクロを実行する毎に作業用シートの内容をクリアするため、データを残す場合は別のシート等に退避させて下さい。

サンプルコード

下記サンプルコードをシートモジュールに貼り付けて下さい。

Sub プロシージャ sample1

Option Explicit

'シートモジュールに貼付
'同一ブック内のシート名をセルに出力
Private Sub sample1()
  Dim i As Long
  Dim j As Long

  Me.Cells.Clear
  MsgBox "シート検索開始"
  j = 1

  For i = 1 To Sheets.Count '・・・(1)
    If Sheets(i).Name <> Me.Name Then '・・・(2)
      Cells(j, 1).Value = Sheets(i).Name '・・・(3)
      j = j + 1    
    End If
  Next i

  MsgBox "シート検索終了"
End Sub

サンプルコード解説

Sub プロシージャ sample1

(1) For i = 1 To Sheets.Count
Sheets.Count でブック内のシート数を取得します。今回の例では既存の3枚のシート+作業用シートで、4が設定されます。

(2) If Sheets(i).Name <> Me.Name Then
Me.Name で自分自身の名前を取得します。今回の例では作業用シートにコードを貼り付けているため、ワークシート名の作業用シートが設定されます。

(3) Cells(j, 1).Value = Sheets(i).Name
Cells(j, 1) でA列(1列目)・j行目のセルを参照します。Cells(行番号,列番号)となるため、Cells(j,2)と記述するとシート名をB列に書き出します。

操作手順

今回は、3枚のシート(Sheet1、Sheet2、Sheet3)が保存されている sample1.xlsx を例に説明します。

  1. シート名を検索するファイルの拡張子を変更する。
  2. 作業用シートを追加する。
  3. サンプルコードを貼り付ける。
  4. サンプルコード sample1 を実行する。

手順1.拡張子の変更
※既にファイルの拡張子が .xlsm になっている場合は、手順2へ進んで下さい。

手順1(1)

(1) 左図の様に sample1.xlsx と表示されていない場合は(2)へ、表示されている場合は(7)へ進んで下さい。

手順1(2)

(2) 任意のフォルダを選択して、ダブルクリックして開いて下さい。
※どのフォルダでもOKです。

手順1(3)

(3) 整理→フォルダーと検索のオプションの順にクリックして下さい。

手順1(4)

(4) 表示タブに切り替え、登録されている拡張子は表示しないの左側にあるチェックボックスのチェックを外して下さい。

手順1(5)

(5) 適用→OKの順にクリックして下さい。

手順1(6)

(6) 左図の様に sample1.xlsx と表示されていることを確認して下さい。

手順1(7)

(7) sample1.xlsx を開き、ファイル→名前を付けて保存の順にクリックして下さい。

手順1(8)

(8) 赤線で囲んだ部分をクリックして下さい。

手順1(9)

(9) 一覧からExcel マクロ有効ブック(*.xlsm)をクリックして下さい。
※ファイルの種類が .xlsm に変更されます。

手順1(10)

(10) 保存をクリックして下さい。
※特に保存場所を指定しない場合は、オリジナルのファイルと同じ場所に保存されます。

手順1(10)※

※(10)で保存をクリックした時に左図の様な警告が出た場合は、いいえ をクリックしてファイルを探してみましょう。


手順2.作業用シートの追加

手順2(1)

(1) 手順1で拡張子を変更したファイル sample1.xlsm を開き、左図の赤線で囲んだ部分をクリックして下さい。
※Excel画面下側のワークシート名が表示されている場所の近くにあります。

手順2(1)※

※(1)の図のアイコンが見つからない方は、左図の赤線で囲まれた部分をクリックして下さい。

手順2(2)

(2) 左図の赤線で囲まれた部分にマウスのカーソルを合わせ、右クリックして下さい。

手順2(3)

(3) 左図の赤線で囲まれた部分をクリックして下さい。

手順2(4)

(4) シート名を 作業用シートに変更して下さい。

手順2(4)※

※(4)でシート名を変更した後に左図の様な警告が出た場合は、重複しない名前に変更して下さい。


手順3.サンプルコードの貼り付け

手順3(1)

(1) 手順2で追加した 作業用シート を選択し、シート名にマウスのカーソルを合わせ、右クリックして下さい。

手順3(2)

(2) 左図の赤線で囲まれた部分をクリックして下さい。

手順3(3)

(3) 左図の赤線で囲まれた部分にサンプルコード Sub プロシージャ sample1 ( Option Explicit ~ End Sub まで )をコピーして貼り付けて下さい。
※サンプルコード1行目の Option Explicit が左図の様に既に表示されている場合は、2行目からコピーして貼り付けて下さい。


手順4.サンプルコードの実行

手順4(1)

(1) デバッグ → VBAProjectのコンパイル の順にクリックして下さい。
※特に問題が無ければ、メッセージは表示されません。

手順4(1)※

※(1) でコンパイル実行後に左図の様な警告が出た場合は、貼り付けた内容を再度確認して下さい。

手順4(2)

(2) 実行 → Sub/ユーザーフォームの実行 の順にクリックして下さい。

手順4(3)

(3) OK をクリックして下さい。

手順4(4)

(4) OK をクリックして下さい。

手順4(5)

(5) シート名が A列に書き出されます。

操作手順 (2回目以降)

上記の操作手順を例に説明します。
マクロを実行する毎に作業用シートの内容をクリアするため、データを残す場合は別のシート等に退避させて下さい。

  1. ファイル sample1.xlsm を開く。
  2. Sub プロシージャ sample1 を表示する。
  3. Sub プロシージャ sample1 を実行する。

手順1.ファイルを開く

手順1

※ sample1.xlsm を開いた時に、左図の様な警告が出た場合は、コンテンツの有効化をクリックして下さい。


手順2.Sub プロシージャ sample1 の表示

手順2(1)

(1) 作業用シートにマウスのカーソルを合わせ、右クリックして下さい。

手順2(2)

(2) 左図の赤線で囲まれた部分をクリックして下さい。


手順3. Sub プロシージャ sample1 の実行

手順3(1)

(1) 左図の赤線で囲まれた部分をクリックして下さい。
※ ( Declarations ) の右側の ▼ をクリックして下さい。

手順3(2)

(2) リストから sample1 をクリックして下さい。

手順3(3)

(3) 実行 → Sub/ユーザーフォームの実行 の順にクリックして下さい。

手順4(4)

(4) OK をクリックして下さい。

手順5(5)

(5) OK をクリックして下さい。

手順3(6)

(6) シート名が A列に書き出されます。

タイトルとURLをコピーしました