EXCEL VBA ワークシートの追加・複数追加・先頭・最後（Worksheets.Addメゾット）

2020年7月13日

EXCEL VBA ワークシートの追加・複数追加・先頭・最後（Worksheets.Addメゾット）

●はじめに

今回は、Worksheets.Addメゾットの使い方を説明いたします。Addメゾットは、シート挿入する場所を指定したり、追加するシート枚数を設定する事が出来ます。EXCELは、複数のシートを管理する事が出来るので、シートの管理はとでも重要になります。まとめてシートを挿入する時や指定する位置にシートを挿入する際は、Worksheets.Addメゾットを利用する事で簡単にシートを追加（操作）する事が出来ます。なお、複数シートを管理する時の注意点として、シート名追加する時は、シート名の重複を回避する必要があります。サンプルプログラム③では、そのシート名の重複回避方法もサンプルプログラムで記載しておりますので、参照して下さい。それでは、順番に説明いたします。

【参照ページ：グラフシートの作成】

EXCEL VBA グラフシートにグラフを作成する方法（Charts.Add : SetSourceData) データ範囲を指定してグラフの作成

【参照ページ：Worksheet・ワークシートの取り扱い】

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

●書式の説明　（Worksheets.Addメゾット）

【Worksheets.Addメゾットの使い方】

●　Worksheets.Add(Before,After,Count,Type)

引数名	必須・オプション	データ型	説明
Before	オプション	Variant	指定したオブジェクトのシートの直前に、新しいシートを追加します。
After	オプション	Variant	指定したオブジェクトのシートの直後に、新しいシートを追加します。
Count	オプション	Variant	追加するシートの数を指定します。既定値は、１になります。
Type	省略可能	Variant	ワークシートの種類を指定します。・xlWorksheet　（ワークシート：既定値）・xlChart　（グラフシート）・xlExcel4IntlMacroSheet　（International Macro シート）・xlExcel4MacroSheet　（ Macro シート）

・【Before】か【After】のいずれかの引数で追加先を指定
・【Count】でワークシートの追加数を指定

『使用例』

ThisWorkbook.Worksheets.Add After:=Worksheets(“Sheet1”), Count:=1

(説明：ワークシート【Sheet1】の次にワークシートを１つ追加します。)

【注意点】

Worksheets.Addメゾットの注意点としては、ワークシートを追加や削除を行う場合は、処理を実行するブックも指定していないと、複数ブックを立ち上げている場合、誤って違うブックにシートを追加する可能性があります。今回、紹介するサンプルプログラムは、全て【ThisWorkbook】を記入していますので、プログラムが記入されているブックが対象となります。

Worksheets.Addメゾットの利用方法（指定追加・前に追加・最後に追加）

●プログラム説明サンプル①　(Worksheets.Addメゾット)

下記のサンプルプログラムは、Worksheets.Addメゾットの利用方法を説明します。ワークシートを追加する際に、指定追加・前に追加・最後に追加する方法を4パターン説明いたします。

'
'******** AKIRA55.COM ******* https://akira55.com/add_worksheet/
'
'パターン①－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd01() 'ワークシート（Sheet1）の後ろにシートが追加されます。


    ThisWorkbook.Worksheets.Add After:=Worksheets("Sheet1"), Count:=1


End Sub

'パターン②－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd02() 'ワークシート（Sheet1）の前にシートが追加されます。


    ThisWorkbook.Worksheets.Add Before:=Worksheets("Sheet1"), Count:=1


End Sub

'パターン③－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd03() '作業中ブックの最後にシートを追加します。


    ThisWorkbook.Worksheets.Add After:=Worksheets(ThisWorkbook.Worksheets.Count), Count:=1


End Sub

'パターン④－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd04()  'ワークシート（大阪）の後ろにシートを２つ追加されます。


    ThisWorkbook.Worksheets.Add After:=Worksheets("大阪"), Count:=2


End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/add_worksheet/

'パターン①－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd01() 'ワークシート（Sheet1）の後ろにシートが追加されます。

ThisWorkbook.Worksheets.Add After:=Worksheets("Sheet1"), Count:=1

End Sub

'パターン②－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd02() 'ワークシート（Sheet1）の前にシートが追加されます。

ThisWorkbook.Worksheets.Add Before:=Worksheets("Sheet1"), Count:=1

End Sub

'パターン③－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd03() '作業中ブックの最後にシートを追加します。

ThisWorkbook.Worksheets.Add After:=Worksheets(ThisWorkbook.Worksheets.Count), Count:=1

End Sub

'パターン④－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Sub WorksheetsAdd04() 'ワークシート（大阪）の後ろにシートを２つ追加されます。

ThisWorkbook.Worksheets.Add After:=Worksheets("大阪"), Count:=2

End Sub

●実行前～実行後　※プログラム実行後、指定した通りにワークシートが追加されました。
（画面クリックして拡大）

指定するワークシートの挿入先に指定するシート名でシートを挿入する。

●プログラム説明サンプル②　(Worksheets.Addメゾット)

下記のサンプルプログラムは、指定するワークシートの挿入先に指定するシート名でシートを挿入するサンプルプログラムです。下記のサンプルプログラムは、ワークシートを複数管理する場合やシートとシートの間に新たにシートを挿入する場合などに利用できると思います。

'
'******** AKIRA55.COM ******* https://akira55.com/add_worksheet/
'

Sub WorksheetsAdd05()  '指定するワークシートの挿入先に指定するシート名で挿入する。

    Dim Ws01 As Worksheet
    Dim SheName, InsName As String
    Dim I, lRow As Long
    
    Set Ws01 = Worksheets("シート名一覧")
    ws01.Select
    lRow = Ws01.Cells(Rows.Count, "A").End(xlUp).Row 'シート「シート名一覧」Ａ列の最終行を取得します。
    
    For I = 2 To lRow  'シート「シート名一覧」Ａ列の最終行分繰り返します。
        
    SheName = Ws01.Cells(I, "A"): InsName = Ws01.Cells(I, "B")  '挿入元と挿入するシート名を代入します。
    
        With ThisWorkbook.Worksheets.Add(After:=Worksheets(SheName), Count:=1)  '指定する場所にシートを挿入します。
            .Name = InsName '挿入したシート名を変更します。
        End With

    Next I

End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/add_worksheet/

Sub WorksheetsAdd05() '指定するワークシートの挿入先に指定するシート名で挿入する。

Dim Ws01 As Worksheet

Dim SheName, InsName As String

Dim I, lRow As Long

Set Ws01 = Worksheets("シート名一覧")

ws01.Select

lRow = Ws01.Cells(Rows.Count, "A").End(xlUp).Row 'シート「シート名一覧」Ａ列の最終行を取得します。

For I = 2 To lRow 'シート「シート名一覧」Ａ列の最終行分繰り返します。

SheName = Ws01.Cells(I, "A"): InsName = Ws01.Cells(I, "B") '挿入元と挿入するシート名を代入します。

With ThisWorkbook.Worksheets.Add(After:=Worksheets(SheName), Count:=1) '指定する場所にシートを挿入します。

.Name = InsName '挿入したシート名を変更します。

End With

Next I

End Sub

●実行前～実行後　※プログラム実行後、ワークシート「シート名一覧」に記載されている【挿入元】と【挿入するシート名】を元に指定された位置に、シートが挿入されました。
（画面クリックして拡大）

ワークシートを挿入した際に、シート名が重複した場合の対処（対応・回避）

●プログラム説明サンプル③　(Worksheets.Addメゾット)

下記のサンプルプログラムは、ワークシートを挿入した際に、挿入したシート名が重複した場合に回避するサンプルプログラムです。VBAプログラムでシートを挿入する際に、同じシート名で挿入するとエラーが発生するので、同じ名前でシートが挿入されないようにする必要があります。

【プログラム内容条件】
●ワークシート「挿入シート」に一覧表示されている「挿入シート」名を挿入します。
●ワークシートを挿入する際に、既に登録されているワークシート名を把握する。
●既に登録されているワークシート名が有る場合は、追加するワークシート名に数字を追記したワークシート名で追加します。
（例）ワークシート名で、「東京」が既に存在して、更に「東京」を追加する場合は、「東京１」で登録します。また、既に「東京１」が有る場合は、「東京２」となります。

'
'
'******** AKIRA55.COM ******* https://akira55.com/add_worksheet/
'
Sub WorksheetsAdd06()  'ワークシートを挿入した際に、シート名が重複した場合の対処（対応・回避）
    Dim Ws, ws01 As Worksheet
    Dim Dic, I, L, lRow As Long
    Dim Temp01, Temp02 As String
    Dim Flag As Boolean
    Dim Keys
       
    Set Dic = CreateObject("Scripting.Dictionary")
    
    Set ws01 = Worksheets("シート名一覧")
    ws01.Select
    For Each Ws In ThisWorkbook.Worksheets  '現在のワークシートを辞書登録します。
         Dic.Add Ws.Name, Ws.Name
    Next Ws
    
    lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row 'シート「シート名一覧」Ａ列の最終行を取得します。
    
    
    For I = 2 To lRow

        Temp01 = Cells(I, "A")  'シート「シート名一覧」のA列のセルデータ（挿入するシート名）を代入します。
                
        Temp02 = Temp01
        
        L = 1  'シートが重複した時の初期値１番～
                            
        Do
                
            If Not Dic.Exists(Temp02) Then
                    Dic.Add Temp02, Temp02  '重複しないシート名を登録します。（辞書登録）
                    Exit Do
                Else
                    Temp02 = Temp01 & L  'シート名が重複した時に連番を付ける。
                    L = L + 1  '連番を加算する。
            End If
            
            If L > 250 Then Exit Sub ' 重複する同じシート名の連番が250を超えたら終了
            
        Loop While 250 > L  '連番作成の最大値250まで設定。
    
    Next I
    
    Keys = Dic.Keys
    
    For I = 0 To Dic.Count - 1  '辞書登録したデータの最終まで繰り返す。
    
        Flag = False  '初期値：Flag　=　False
        
        For Each Ws In ThisWorkbook.Worksheets  '現在のワークシートに対してこれから登録するシート名が有るか調べます。
            If Ws.Name = Keys(I) Then Flag = True  '既にワークシートが存在していれば、True
        Next Ws
        
         If Flag = False Then 'ワークシート名が無ければ追加する。
                ThisWorkbook.Worksheets.Add After:=Worksheets(ThisWorkbook.Worksheets.Count), Count:=1 'シートを挿入します。
                ActiveSheet.Name = Keys(I) '挿入したシート名を変更します。
         End If

                
    Next I
    
    Set Dic = Nothing
    
    
End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/add_worksheet/

Sub WorksheetsAdd06() 'ワークシートを挿入した際に、シート名が重複した場合の対処（対応・回避）

Dim Ws, ws01 As Worksheet

Dim Dic, I, L, lRow As Long

Dim Temp01, Temp02 As String

Dim Flag As Boolean

Dim Keys

Set Dic = CreateObject("Scripting.Dictionary")

Set ws01 = Worksheets("シート名一覧")

ws01.Select

For Each Ws In ThisWorkbook.Worksheets '現在のワークシートを辞書登録します。

Dic.Add Ws.Name, Ws.Name

Next Ws

lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row 'シート「シート名一覧」Ａ列の最終行を取得します。

For I = 2 To lRow

Temp01 = Cells(I, "A") 'シート「シート名一覧」のA列のセルデータ（挿入するシート名）を代入します。

Temp02 = Temp01

L = 1 'シートが重複した時の初期値１番～

If Not Dic.Exists(Temp02) Then

Dic.Add Temp02, Temp02 '重複しないシート名を登録します。（辞書登録）

Exit Do

Else

Temp02 = Temp01 & L 'シート名が重複した時に連番を付ける。

L = L + 1 '連番を加算する。

End If

If L > 250 Then Exit Sub ' 重複する同じシート名の連番が250を超えたら終了

Loop While 250 > L '連番作成の最大値250まで設定。

Next I

Keys = Dic.Keys

For I = 0 To Dic.Count - 1 '辞書登録したデータの最終まで繰り返す。

Flag = False '初期値：Flag　=　False

For Each Ws In ThisWorkbook.Worksheets '現在のワークシートに対してこれから登録するシート名が有るか調べます。

If Ws.Name = Keys(I) Then Flag = True '既にワークシートが存在していれば、True

Next Ws

If Flag = False Then 'ワークシート名が無ければ追加する。

ThisWorkbook.Worksheets.Add After:=Worksheets(ThisWorkbook.Worksheets.Count), Count:=1 'シートを挿入します。

ActiveSheet.Name = Keys(I) '挿入したシート名を変更します。

End If

Next I

Set Dic = Nothing

End Sub

●実行前～実行後　※プログラム実行後、ワークシート「シート名一覧」に一覧表示されている「挿入シート」名をシート追加します。シート追加する際は、重複されている場合は、シート名に連番を付けてシート挿入されました。シート名が重複されていなければ、そのままシート名が挿入されました。
（画面クリックして拡大）