同一ブック内の非表示以外のシート名をセルに書き出す

概要

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

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

サンプルコード

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

Sub プロシージャ sample2

Option Explicit

'シートモジュールに貼付
'ブック内のシート名出力 (非表示状態を除く)
Private Sub sample2()
  Dim i As Long
  Dim j As Long

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

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

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

サンプルコード解説

Sub プロシージャ sample2

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

(2) If Sheets(i).Visible = True Then
Sheets(i).Visible で i 番目のシートの表示状態を取得します。True は表示されている状態です。

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

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

操作手順

今回は、3枚のシート(Sheet1、Sheet2、Sheet3)が保存されている sample2.xlsx を例に説明します。 Sheet2は非表示状態になっています。

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

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

手順1(1)

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

手順1(2)

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

手順1(3)

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

手順1(4)

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

手順1(5)

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

手順1(6)

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

手順1(7)

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

手順1(8)

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

手順1(9)

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

手順1(10)

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

手順1(10)※

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

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

手順2(1)

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

手順2(2)※

※(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 プロシージャ sample2 ( Option Explicit ～ End Sub まで )をコピーして貼り付けて下さい。
※サンプルコード1行目の Option Explicit が左図の様に既に表示されている場合は、2行目からコピーして貼り付けて下さい。

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

手順4(1)