シートタブの操作　味はソコソコ？Excel Tips（しお味）

シートタブの操作関係

Excelで1年分のデータを取り扱う場合、1か月分を一枚のシートにして月が変わるたびに、新たにシートを追加していく様な使い方をすることがよくあります。その場合、少し数が増えてくるとシートタブの命名、タブの整列等々手動だと面倒な作業になります。そこで、今回はシートタブの操作を自動化する事を考えてみます。

ここで用いるのはSheetsオブジェクト関連です。
なにか操作する場合はSheetsオブジェクトのメソッド
情報を得る場合はSheetsオブジェクトのSheetsプロパティ
になります。
（公式情報）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.sheets

シートタブの情報を取得する

この例は単純に、現在のシートタブの情報を取得して、表示しています。

●処理の概要
1）全シート数の取得　countプロパティ
Worksheets.Count
2）シート名の取得（配列に取り込む）nameプロパティ
stagn(k) = Worksheets(k).Name
3）シート上に表示
Worksheets("sheet1").Cells(10 + s, 2) = stagn(s)　

以下、そのコードです。

  Sub gsinfo()      '■■■シート情報の取得■■■

  Dim stagn(20) As String             'シート名の収容 シートタブ数により配列数は決める
  Dim snmb As Integer                 'シート数の収容

  'シート名の情報取得***********************************

  snmb = Worksheets.Count             '全シート数の取得

  For k = 1 To snmb

      stagn(k) = Worksheets(k).Name   'シート名の取得

  Next

  'シート情報の表示*************************************

  For s = 1 To snmb

      Worksheets("sheet1").Cells(10 + s, 2) = stagn(s)      'シート名の表示

  Next

      Worksheets("sheet1").Cells(11, 4) = snmb              'シート数の表示

  End Sub

シートタブ情報の取得（ダウンロード）

シートタブの情報を取得して並べ替える

並べ替え前	並べ替え後

先ほどのシートタブの情報を取得て、それを元に実際のタブを並び替えます。
並び替えの処理は、配列変数に取り込んでやる方法もありますが、ここでは一旦シート上にデータを展開して元々のExcelの並べ替え機能を使います。数多くのシートを整然と扱うには必須の処理となります。

ここで用いるのはSortFields.Add2 メソッドです。
並び替え処理をマクロの記録をしてみるとこのメソッドが出てきます。
（公式情報）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.sortfields.add2

1）タブ情報を取得
2）データを一旦シート上に展開
3）Excelの並び替え機能で並び替える（昇順xlAscending、降順xlDescending）
Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B11:B29"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
4）並び替えたデータをもとにタブの並び替え処理を行う
tsnme = Worksheets("Sheet1").Cells(k + 10, 2) 'シート名の一時収容
Worksheets(tsnme).Move Before:=Worksheets(k) 'シートの並びに反映

以下、そのコードです。（昇順）

  Sub sort1()  '■■■昇順ソート■■■

  Call gsinfo                                           'シートタブ情報の取得

      snmb = Worksheets.Count                           '全シート数の取得

      Range("B11").Select
      ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
      ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B11:B29"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ActiveWorkbook.Worksheets("Sheet1").Sort
          .SetRange Range("B11:B29")
          .Header = xlNo
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With


  Dim tsnme As String                                   'シート名の一時収容用

  For k = 1 To snmb

       tsnme = Worksheets("Sheet1").Cells(k + 10, 2)    'シート名の一時収容
       Worksheets(tsnme).Move Before:=Worksheets(k)     'シートの並びに反映
  Next

  Call gsinfo                                           'シートタブ情報の取得（再表示）

  Worksheets("Sheet1").Select                           'Sheet1にフォーカス

  End Sub

シートタブの整列（ダウンロード）

シートタブの一部を固定する

並べ替え前	並べ替え後（一部固定）

シートタブの並べ替えをすると、シートの把握がやりやすくなりますが、場合によりメインのシートが思いもよらない場所になってしまう場合があります。その様な場合、特定のシートの位置を決め打ちで固定することが出来ます。
今回の例はsheet1を左端に固定します。この様にメインのシートを固定しておけば、シートを追加したり、ソートしたりしてもメインのシートは常に左端にあり操作性が良くなります。

●特定シートを固定する（以下の場合は固定シートは"Sheet1"、"原票"とする）
1）1番目の固定設定（左端）
Worksheets("Sheet1").Move Before:=Worksheets(1)
2）2番目の固定設定（左端から2番目）
Worksheets("原票").Move Before:=Worksheets(2)
この様に固定化するシートは自由に設定出来ます。

単純化するため固定するシートを"sheet1"のみの場合の例を示します
以下、そのコードになります。（情報取得の部分）

  Sub gsinfo()      '■■■シート情報の取得■■■

  Dim stagn(20) As String             'シート名の収容 シートタブ数により配列数は決める
  Dim snmb As Integer                 'シート数の収容

  '特定シートを固定する（このサンプルの場合は固定シートはSheet1とする）
  Worksheets("Sheet1").Move Before:=Worksheets(1)    '1番目の固定設定

  'シート名の情報取得***********************************

  snmb = Worksheets.Count             '全シート数の取得

  For k = 1 To snmb

      stagn(k) = Worksheets(k).Name   'シート名の取得

  Next

  'シート情報の表示*************************************

  For s = 1 To snmb

      Worksheets("sheet1").Cells(10 + s, 2) = stagn(s)      'シート名の表示

  Next

      Worksheets("sheet1").Cells(11, 4) = snmb              'シート数の表示

  End Sub

以下、そのコードになります。（並べ替えの部分　昇順の場合）

  Sub sort1()  '■■■昇順ソート■■■

'特定シートを固定する（このサンプルの場合は固定シートはSheet1とする）
Worksheets("Sheet1").Move Before:=Worksheets(1)    '1番目の固定設定

Call gsinfo                                           'シートタブ情報の取得

    snmb = Worksheets.Count                           '全シート数の取得

    Range("B11").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B11:B29"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("B11:B29")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Dim tsnme As String                                   'シート名の一時収容用

For k = 1 To snmb

     tsnme = Worksheets("Sheet1").Cells(k + 10, 2)    'シート名の一時収容
     Worksheets(tsnme).Move Before:=Worksheets(k)     'シートの並びに反映
Next


Call gsinfo                                           'シートタブ情報の取得（再表示）

Worksheets("Sheet1").Select                           'Sheet1にフォーカス

シートタブの固定（ダウンロード）

シートを追加、コピー、削除する

追加前	追加後（一部コピー）

上記の操作（情報取得、並び替え、固定）を踏まえてさらにシートの追加やコピーをする処理を説明します。
この場合は、「main」と「原紙」シートを固定した状態で、シートの単純追加（空のシート追加）や原紙シートをコピーして新たなシートを追加する処理を行います。

まずは「シートの追加」です。単純に空のシートを追加します。

ここで用いるのはWorksheets.addメソッドになります。
（公式情報）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.sheets.add

●シートを追加する
構文
Worksheets.Add (Before, After, Count, Type)

以下、シートの追加のコードです。

  Sub addst1()        '■■■シートの追加■■■

  Dim snmb As Integer                 'シート数の収容

  Dim ws1 As Worksheet                'ワークシート名変数
  Dim ws2 As Worksheet                'ワークシート名変数

  Set ws1 = Worksheets("main")
  Set ws2 = Worksheets("原紙")

  '特定シートを固定する（このサンプルの場合は固定シートは2シートとする）
  Worksheets("main").Move Before:=Worksheets(1)    '1番目の固定設定
  Worksheets("原紙").Move Before:=Worksheets(2)    '2番目の固定設定

  snmb = Worksheets.Count             'シート数の取得

  Worksheets("main").Select            '表示シート選択
  Worksheets.Add After:=Worksheets("原紙"), Count:=1        'シートを追加する（原紙シートの後に追加）

  ActiveSheet.Name = "売上データ" & snmb                    '名前の付与（追加したシート名を設定）

  Call getsinfo                       '情報表示

  Worksheets("main").Select           '表示シート選択

  End Sub

今度は、原紙をコピーしてそのシートを追加します。

ここで用いるのはWorksheets.Copy メソッドになります。
（公式情報）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheets.copy

●シートをコピーする
構文
Worksheets.Copy(Before, After)

以下、シートのコピーのコードです。

  Sub cpyst1()        '■■■シートのコピー■■■

  Dim snmb As Integer                 'シート数の収容

  snmb = Worksheets.Count             'シート数の取得

  Dim ws1 As Worksheet                'ワークシート名変数
  Dim ws2 As Worksheet                'ワークシート名変数

  Set ws1 = Worksheets("main")
  Set ws2 = Worksheets("原紙")

  '特定シートを固定する（このサンプルの場合は固定シートは2シートとする）
  ws1.Move Before:=Worksheets(1)    '1番目の固定設定
  ws2.Move Before:=Worksheets(2)    '2番目の固定設定

  ws2.Copy After:=Worksheets(Worksheets.Count)   'シートをコピーする

  ActiveSheet.Name = "入力データ" & snmb         '名前の付与

  Call getsinfo                       '情報表示

  Worksheets("main").Select           '表示シート選択

  End Sub

さらに、シートの削除を行います。
この場合、シート総数を取得してその情報を元に一番右端のシートを削除します。

ここで用いるのはWorksheets.Copy メソッドになります。
（公式情報）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheet.delete

●シートを削除する
構文
Worksheets.delete

以下、シートの削除のコードです。

  Sub delst1()        '■■■シートの削除■■■

  Dim cntsn As Integer                 'シート数の収容

  cntsn = Worksheets.Count             'シート数の取得

  If cntsn = 2 Then                    '固定シートの削除防止

      Exit Sub

  End If

  Application.DisplayAlerts = False    '削除のアラ－トダイアログ無効

  Worksheets(cntsn).Delete                 'シートの削除

  Application.DisplayAlerts = True     '削除のアラ－トダイアログ有効

  Call getsinfo                        '情報表示

  Worksheets("main").Select            '表示シート選択

  End Sub

以上の操作を踏まえて、まとめたものを下記のファイルにしています。
シートタブの操作（追加、コピー、削除）（ダウンロード）

シートを追加、コピー、削除する（まとめ）
以上のTIPSをまとめたファイルです。お時間の無い方におまとめ編です。いつも同じタブ位置の固定シート（main、原紙）と、追加やコピーされる変動シートがありあす。実運用ではこの様に、固定されたシートと変動するシートがある様な状態になる場合が多いと思います。

TIPS_シートタブの操作（まとめ）