Excel VBA シートのコピー、移動、表示/非表示、ループ

VBAでシートを操作する方法についてご説明します。

シートのコピー、移動、表示、非表示、ループの方法です。

シートの選択/取得/追加/削除については、「Excel VBA シートの選択、取得、追加、削除」をご覧下さい。

選択から削除の内容と合わせて、シートの基本操作は一通り対応できるかと思います。

1.シートのコピー
2.シートの移動
3.シートの表示/非表示
4.シートのループ
5.まとめ

1.シートのコピー

シートのコピーには、「Copy」メソッドを使用します。

引数に「Before」と「After」を指定する事で、コピー先の位置を指定することができます。

Sheet1をSheet2の後ろに追加しました。

この時、すでにSheet1が存在するため、自動的に「Sheet1 (2)」と名前が割り当てられました。

Sub Sample1()

Worksheets("Sheet1").Copy After:=Worksheets("Sheet2")

End Sub

コピーしてそのまま、シート名を変更する場合は下記の様に変更します。

Sub Sample2()

Worksheets("Sheet1").Copy After:=Worksheets("Sheet2")
ActiveSheet.Name = "テスト"

End Sub

また、別ブックを指定する事で、別のブックにコピーすることもできます。

「SampleFile1.xlsx」というExcelファイルのSheet1の後ろに、Sheet1をコピーしてからシート名を変更しています。

この時ペースト先のファイルを開いていないとエラーになります。

Sub Sample3()

Worksheets("Sheet1").Copy After:=Workbooks("SampleFile1.xlsx").Worksheets("Sheet1")
Workbooks("SampleFile1.xlsx").ActiveSheet.Name = "テスト"

End Sub

2.シートの移動

シートの移動方法は、「Move」メソッドを使用します。

コピー同様に、引数に「Before」と「After」を指定する事で、移動先の位置を指定することができます。

もし、引数を省略した場合は新しいブックが生成されて、新規ブックに移動されます。

計算結果等をあえて新しいブックとして出力する場合など、意外と使用頻度が高いかもしれません。

次のコードはSheet1を新規ブックに移動して、テストというシート名に変更しました。

Sub Sample4()

Worksheets("Sheet1").Move
ActiveSheet.Name = "テスト"

End Sub

もちろん、別ブックを指定して移動も可能です。

Copyの部分をMoveに変更しただけです。

Sub Sample5()

Worksheets("Sheet1").Move After:=Workbooks("SampleFile1.xlsx").Worksheets("Sheet1")
Workbooks("SampleFile1.xlsx").ActiveSheet.Name = "テスト"

End Sub

3.シートの表示/非表示

シートの表示、非表示はWorksheetオブジェクトのVisibleのTrue/Falseを切り替える事で、操作できます。

作成したツールを他の人に配布等する際に、作業シートを一時的に非表示にする場合などに使用する事が多いかと思います。

注意点としては非表示のシートをSelectするとエラーになります。

非表示シートを指定する場合はActivateが良いと思います。

シートの表示

Sheet1を非表示の状態から表示します。

Sub Sample6()

Worksheets("Sheet1").Visible = True

End Sub

シートの非表示

Sheet1を非表示にします。

Sub Sample7()

Worksheets("Sheet1").Visible = False

End Sub

メニュー操作を制御する

基本的にVBAで表示/非表示を切り替えても、メニューからも切り替える事が可能です。

しかし、「xlVeryHidden」で非表示にすると、メニューから表示に切り替える事ができなくなります。

再表示自体にシート名が表示されません。

「xlVeryHidden」で非表示にしたシートは「Visible=True」で表示する事ができます。

※「xlVeryHidden」で非表示にしたまま放置は絶対やめましょう！(存在を忘れる)

Sub Sample8()

Worksheets("Sheet1").Visible = xlVeryHidden

End Sub

4.シートのループ

最後にシートのループ方法です。

ループの基本は「ExcelVBA 「For~Next」「Do~Loop」ループの使い方」をご覧ください。

こちらでは「For~Next」と「For Each In Next」の方法をご説明します。

For~Nextでシートを操作する

「For~Next」でシートを操作する場合は、シートの指定をインデックス番号で指定します。

シート数はWorksheets.Countで取得出来ます。

もちろん、ループの開始と終了を指定する事も可能です。

次のコードはSheet1~最終シートまでループしてシート名を取得します。

Sub Sample9()

Dim i       As Long
Dim ShName  As String

For i = 1 To Worksheets.Count 'シート1から最終シートまでループ

    ShName = ShName & Worksheets(i).Name & vbCrLf '順番にシート名を取得(※「 & vbCrLf」は改行です)
    
Next i

MsgBox ShName

End Sub

For Each In Nextでシートを操作する

「For Each In Next」はオブジェクトのコレクションを操作するループとなるため、シンプルにループで操作する事ができます。

Sub Sample10()

Dim mySh    As Worksheet
Dim ShName  As String

For Each mySh In Worksheets 'シート全てループ

    ShName = ShName & mySh.Name & vbCrLf '順番にシート名を取得(※「 & vbCrLf」は改行です)
    
Next

MsgBox ShName

End Sub

シートをループで削除する方法

シートを「For~Next」と「For Each In Next」で削除する方法を、それぞれサンプルコードを記載します。

For~Nextで削除

シートを削除する場合は必ず最終シートから前へ「Step-1」でループするようにしてください。

1シートのみ削除してループを離脱する場合は、いいのですが基本的には使い分ける必要性もないので、「削除は後ろから」と運用した方が無難です。

理由は、削除した場合に本来次に来るインデックスが詰められてしまうためです。

1,2,3,4,5,6,7とシートがあった時に、「3」を削除した場合に3以降の「4~7」が「3~6」とインデックスが再定義されるためです。

つまり削除前の「４」はスルーされます。

Sub Sample11()

Dim i       As Long
Dim ShName  As String

Application.DisplayAlerts = False '確認メッセージを非表示

For i = Worksheets.Count To 1 Step -1 '最終シートからシート1までループ

    If Worksheets(i).Name <> "Sheet1" Then 'Sheet1以外削除

        Worksheets(i).Delete
    
    End If
    
Next i

End Sub

For Each In Nextで削除

「For~Next」と違い、For Eachの場合は削除する場合でも順番を意識する必要もありません。

Sub Sample12()

Dim mySh    As Worksheet
Dim ShName  As String

Application.DisplayAlerts = False '確認メッセージを非表示

For Each mySh In Worksheets 'シート全てループ

    If mySh.Name <> "Sheet1" Then 'Sheet1以外削除

        mySh.Delete
    
    End If
    
Next

End Sub

結果として、コードは両方比較しても好みで使いたい方を使って問題程度の違いです。

シート数やループの順番等を気にしなくていいので、使いやすさとしては「For Each」かなと思います。