EXCEL VBA Worksheet・ワークシートの取り扱い・指定・参照・追加・削除方法

2020年3月10日

EXCEL VBA Worksheet・ワークシートの取り扱い・指定方法

●はじめに

ここでは、Worksheetsの取り扱う方法やワークシートを参照するオブジェクト変数の指定方法・ワークシート名の変更方法・ワークシートの自動挿入や自動削除などの方法を説明いたします。EXCEL　VBAでは、ワークシート間で集計処理や別のワークシートを参照するプルグラムを作成する事があります。ワークシート名をオフジェクト変数に指定する事により、Cells・Rangeなどを使う時に、ワークシート名をオブジェクト変数にする事で、プログラムを分かり易く記述する事ができます。

※オブジェクト変数とは、EXCELのブック・シート・セルなどを変数として取り扱う事を言う。

Worksheet　シートをオブジェクト変数で参照設定

●プログラム説明(Sheet:サンプルプログラム①）
Dim　変数名　As　Worksheet
Set 変数名　＝　オブジェクトへの参照　

下記のサンプルプログラムは、ワークシート（Sheet１～３）をオブジェクト変数（WS01～０３）として設定し、各ワークシートのセル（A1）に数値を代入し、その数値の合計を算出して、合計結果をメッセージボックスに表示します。

Sub Worksheets01()

    Dim wsX, ws01, ws02, ws03 As Worksheet  '変数としてWorksheet型で指定します。
    
    Dim Gokei As Integer
    
    Set ws01 = Worksheets("Sheet1")    'ws01を”Sheet1”変数名として指定。
    Set ws02 = Worksheets("Sheet2")    'ws02を”Sheet2”変数名として指定。
    Set ws03 = Worksheets("Sheet3")    'ws03を”Sheet3”変数名として指定。
    
    ws01.Cells(1, "A") = 100     'Sheet1のセルA１に”100”を代入
    ws02.Cells(1, "A") = 20      'Sheet2のセルA１に”20”を代入
    ws03.Cells(1, "A") = 3       'Sheet3のセルA１に”3”を代入
    
    For Each wsX In Worksheets    '全てのSheetを順番に繰り返す
   
        Gokei = Gokei + wsX.Cells(1, "A")   '順番に繰り返すセルのA1に代入されている数値を加算する。
    
    Next
    
    MsgBox Gokei  'A1に加算された結果が表示されます。
    

End Sub

Sub Worksheets01()

Dim wsX, ws01, ws02, ws03 As Worksheet '変数としてWorksheet型で指定します。

Dim Gokei As Integer

Set ws01 = Worksheets("Sheet1") 'ws01を”Sheet1”変数名として指定。

Set ws02 = Worksheets("Sheet2") 'ws02を”Sheet2”変数名として指定。

Set ws03 = Worksheets("Sheet3") 'ws03を”Sheet3”変数名として指定。

ws01.Cells(1, "A") = 100 'Sheet1のセルA１に”100”を代入

ws02.Cells(1, "A") = 20 'Sheet2のセルA１に”20”を代入

ws03.Cells(1, "A") = 3 'Sheet3のセルA１に”3”を代入

For Each wsX In Worksheets '全てのSheetを順番に繰り返す

Gokei = Gokei + wsX.Cells(1, "A") '順番に繰り返すセルのA1に代入されている数値を加算する。

MsgBox Gokei 'A1に加算された結果が表示されます。

End Sub

●実行中　※sheet１のA1には、１００を代入・Sheet２のA１には、２０を代入・Sheet３のA1には、３を代入します。（クリックして拡大）

●実行後　※Sheet１～３のA1に代入されている値を全ての加算した結果が表示されます。（１００＋２０＋３＝１２３）　（クリックして拡大）

Worksheet　.Nameプロパティの設定（シート名の変更）

●プログラム説明(Sheet:サンプルプログラム②）

次のプログラムは、ワークシート名を入力して、全てのシートに対してワークシート名を変更します。

Sub Worksheets02()

    Dim ws As Worksheet
    
    For Each ws In Worksheets '全てのワークシートを対象

        ws.Name = InputBox("現在のワークシート名は、" & ws.Name & "です。変更するワークシート名を入力して下さい。")

    Next
    
End Sub

Sub Worksheets02()

Dim ws As Worksheet

For Each ws In Worksheets '全てのワークシートを対象

ws.Name = InputBox("現在のワークシート名は、" & ws.Name & "です。変更するワークシート名を入力して下さい。")

End Sub

●実行後　※ワークシートの名前を順番に入力して変更します。（クリックして拡大）

Worksheets　.Deleteプロパティの設定（ワークシートの削除・指定するワークシートの削除）

●プログラム説明(Sheet:サンプルプログラム③）

次のプログラムは、指定したワークシート名以外のシートを削除するプログラムです。

Sub Worksheets03()

    Dim wsX As Worksheet
    
    Application.DisplayAlerts = False  'マクロ実行中の警告メッセを止める
    
    For Each wsX In Worksheets
        If wsX.Name <> "元シート" And wsX.Name <> "LIST" Then
            wsX.Delete        '元シート及びListのシートを残して全てのシートを削除する。
        End If
    Next
    
    Application.DisplayAlerts = True  'マクロ実行中の警告メッセージを表示(再開)させる

End Sub

Sub Worksheets03()

Dim wsX As Worksheet

Application.DisplayAlerts = False 'マクロ実行中の警告メッセを止める

For Each wsX In Worksheets

If wsX.Name <> "元シート" And wsX.Name <> "LIST" Then

wsX.Delete '元シート及びListのシートを残して全てのシートを削除する。

End If

Application.DisplayAlerts = True 'マクロ実行中の警告メッセージを表示(再開)させる

End Sub

※ワークシートをマクロプログラムで削除する際に、上記の警告メッセージが表示（一時停止）されるので、下記、プログラムを追加する事で、メッセージを表示させずに、実行する事ができます。
Application.DisplayAlerts = False ‘マクロ実行中の警告メッセを止める
Application.DisplayAlerts = True ‘マクロ実行中の警告メッセージを表示(再開)させる

●実行前　※複数のワークシートが表示されています。赤枠の指定したワークシート以外を削除します。（クリックして拡大）

●実行後　※上記プログラムを実行した結果です。指定した「元シート・LIST」以外のシートが削除されました。（クリックして拡大）

Worksheets.Addプロパティの設定（ワークシートの追加・指定したシート名で追加する）

●プログラム説明(Sheet:サンプルプログラム④）

次のプログラムは、新たにワークシートを追加します。追加するワークシートは、ひな形となるワークシートをコピーしつつ、指定したワークシート名で追加するプログラムです。
ワークシートをコピーするひな形として「ひな形シート」をコピーしてワークシートを追加します。追加するワークシート名は、「名前リスト」シートを参照します。

Sub Worksheets04()

    Dim wsX, wsL As Worksheet
    Dim L As Integer
    
    Set wsX = Worksheets("ひな形シート")
    Set wsL = Worksheets("名前リスト")
    
    
    wsX.Cells.Copy    'ひな形シートをコピーする。
    
    For L = 2 To 10
    
        Worksheets.Add after:=Worksheets(ActiveSheet.Index)  'ワークシートを追加する。
        ActiveSheet.Name = wsL.Cells(L, "A")  '名前リストからシート名を設定します。
        ActiveSheet.Paste  '新しいワークシートにひな形シートを張り付けます。

    Next L

End Sub

Sub Worksheets04()

Dim wsX, wsL As Worksheet

Dim L As Integer

Set wsX = Worksheets("ひな形シート")

Set wsL = Worksheets("名前リスト")

wsX.Cells.Copy 'ひな形シートをコピーする。

For L = 2 To 10

Worksheets.Add after:=Worksheets(ActiveSheet.Index) 'ワークシートを追加する。

ActiveSheet.Name = wsL.Cells(L, "A") '名前リストからシート名を設定します。

ActiveSheet.Paste '新しいワークシートにひな形シートを張り付けます。

Next L

End Sub

●実行前　※事前にひな形シートと名前リストを作成します。この内容を元に、ワークシートを追加します。（クリックして拡大）

●実行後　※ひな形シートの内容が名前リストを元に、ワークシートが追加されました。（クリックして拡大）

●プログラム説明(Sheet:サンプルプログラム⑤）

次のプログラムは、サンプルプログラム③と④を組み合わせたプログラムになります。まず、プログラムを見てどのような処理をするのか考えて下さい。

Sub Worksheets05()

    Dim wsX, wsL As Worksheet
    Dim L As Integer
    Dim Sname As String
    
    
    Application.DisplayAlerts = False  'マクロ実行中の警告メッセを止める
    
    For Each wsX In Worksheets
        If wsX.Name <> "名前リスト" And wsX.Name <> "ひな形シート" Then
            wsX.Delete        'ワークシートの削除
        End If
    Next
    
    Application.DisplayAlerts = True  'マクロ実行中の警告メッセージを表示(再開)させる
    
    
    Set wsX = Worksheets("ひな形シート")
    Set wsL = Worksheets("名前リスト")
    
    
    wsX.Cells.Copy
    
    For L = 2 To 10
    
        Worksheets.Add after:=Worksheets(ActiveSheet.Index)　’ワークシートの追加
        ActiveSheet.Name = wsL.Cells(L, "A")　’ワークシートの名前の変更
        ActiveSheet.Paste　’ひな形を張り付け

    Next L

End Sub

Sub Worksheets05()

Dim wsX, wsL As Worksheet

Dim L As Integer

Dim Sname As String

Application.DisplayAlerts = False 'マクロ実行中の警告メッセを止める

For Each wsX In Worksheets

If wsX.Name <> "名前リスト" And wsX.Name <> "ひな形シート" Then

wsX.Delete 'ワークシートの削除

End If

Application.DisplayAlerts = True 'マクロ実行中の警告メッセージを表示(再開)させる

Set wsX = Worksheets("ひな形シート")

Set wsL = Worksheets("名前リスト")

wsX.Cells.Copy

For L = 2 To 10

Worksheets.Add after:=Worksheets(ActiveSheet.Index)　’ワークシートの追加

ActiveSheet.Name = wsL.Cells(L, "A")　’ワークシートの名前の変更

ActiveSheet.Paste　’ひな形を張り付け

Next L

End Sub

最後まで、ご覧いただきまして誠に有難うございました。
以上で、Worksheet(ワークシート)に関する説明でした。EXCEL　VBAでは、シートを参照する事は頻繁に発生するので、サンプルプルグラム等を参照に理解して下さい。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

VBA基礎EXCEL, VBA