Excel VBA 複数シートを効率的に管理！ワークシート操作の複数シートを効率的に管理！ワークシート操作のベストテクニック5選

2023年5月8日

Excel VBA 複数シートを効率的に管理！ワークシート操作の複数シートを効率的に管理！ワークシート操作のベストテクニック5選

●はじめに

今回説明するのは、Excel VBAを使用したワークシートのスムーズな操作方法についてご紹介します。今回説明するサンプルプログラムでは、複数シートの管理制御や印刷作業を効率化するためのベストテクニックに焦点を当てます。Worksheetオブジェクトの使い方やPrintOutメソッド、PrintPreviewメソッドの詳細な説明を通じて、具体的な手順とサンプルプログラムを提供します。さらに、ワークブックの整理やシート名の並べ替え、一括置換、データの差分比較、ワークブックの保護・保護解除など、実務で役立つテクニックも解説いたします。詳細につきましては、下記を参照して下さい。

●【EXCEL VBA 別のブックからデータをコピーする。については、下記を参照して下さい】

EXCEL VBA 別のブックからデータをコピーする（ブック間のシートコピー）（テクニック）

●【EXCEL　VBAワークブックの保護・解除については、下記を参照して下さい】

EXCEL VBA ワークブック（WorkBook）の保護・解除の方法（Protect / Unprotect)

●【EXCEL　VBAワークシートの並び替えについては、下記を参照して下さい】

EXCEL VBA ワークシートの並び替え・任意の順番・昇順・降順・複数シートの移動（Moveメゾット）

●【Worksheet オブジェクト (Excel) 、下記を参照して下さい】（Microsoft社　様）】
https://learn.microsoft.com/ja-jp/office/vba/api/excel.worksheet

●書式の説明

●　PrintOut メソッドを利用するには、下記の通りに設定を行います。

用途: ワークシート、ワークブック、または範囲を印刷するために使用されます。
構文説明: expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)
・expression: 必須。ワークシート、ワークブック、または範囲オブジェクトを指定します。
・From: 省略可能。印刷開始ページ番号を指定します。
・To: 省略可能。印刷終了ページ番号を指定します。
・Copies: 省略可能。印刷するコピー数を指定します。
・Preview: 省略可能。Trueを指定すると、印刷プレビューが表示されます。Falseを指定すると、印刷プレビューなしで印刷が実行されます。
・ActivePrinter: 省略可能。印刷するプリンターを指定します。
・PrintToFile: 省略可能。Trueを指定すると、印刷内容をファイルに出力します。Falseを指定すると、プリンターで印刷します。
・Collate: 省略可能。Trueを指定すると、印刷物が整理されます。Falseを指定すると、整理されません。
・PrToFileName: 省略可能。PrintToFileがTrueの場合、出力するファイル名を指定します。
・IgnorePrintAreas: 省略可能。Trueを指定すると、印刷範囲を無視して印刷します。Falseを指定すると、設定された印刷範囲に従って印刷します。

●　PrintPreviewメソッドを利用するには、下記の通りに設定を行います。

用途: ワークシート、ワークブック、または範囲の印刷プレビューを表示するために使用されます。
構文説明: expression.PrintPreview(EnableChanges)
・expression: 必須。ワークシート、ワークブック、または範囲オブジェクトを指定します。
・EnableChanges: 省略可能。Trueを指定すると、印刷プレビュー画面での設定変更が可

Excel VBA　選択した複数のワークシートに対して印刷プレビューで確認してから印刷する方法

●プログラム説明　(サンプル①)

下記のサンプルプログラムば、指定されたシート（今回の場合はSheet1とSheet2）の印刷プレビューを表示します。プレビューが表示された後、メッセージボックスが表示され、シートを印刷するかどうか尋ねます。 [はい] をクリックすると、シートが印刷されます。 [いいえ] をクリックすると、印刷せずに次のシートのプレビューに進みます。

【プログラムの流れ】
①対象となるシート名の配列を作成
②配列内の各シートについて、以下の処理を行う
a. シートオブジェクトを設定
b. シートの印刷プレビューを表示
c. メッセージボックスで印刷するか尋ねる
d. シートを印刷するか、次のシートに進む能になります。Falseを指定すると、設定変更ができません。

【プログラム実行条件・注意事項】
①　対象とするシート名が正確でないと、エラーが発生します。
②　シートが存在しない場合、エラーが発生します。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　SheetWork01（サンプルプログラム）

Sub PrintSheetsWithPreview()
    Dim ws As Worksheet
    Dim sheetNames() As Variant
    Dim i As Integer

    ' 対象シート名の配列を作成
    sheetNames = Array("Sheet1", "Sheet2")

    ' 配列内の各シートについて処理を行う
    For i = LBound(sheetNames) To UBound(sheetNames)
        ' シートオブジェクトを設定
        Set ws = Worksheets(sheetNames(i))
        
        ' シートの印刷プレビューを表示
        ws.PrintPreview
        
        ' メッセージボックスで印刷するか尋ねる
        If MsgBox("このシートを印刷しますか？", vbYesNo) = vbYes Then
            ' シートを印刷する
            ws.PrintOut
        End If
    Next i
End Sub

Sub PrintSheetsWithPreview()

Dim ws As Worksheet

Dim sheetNames() As Variant

Dim i As Integer

' 対象シート名の配列を作成

sheetNames = Array("Sheet1", "Sheet2")

' 配列内の各シートについて処理を行う

For i = LBound(sheetNames) To UBound(sheetNames)

' シートオブジェクトを設定

Set ws = Worksheets(sheetNames(i))

' シートの印刷プレビューを表示

ws.PrintPreview

' メッセージボックスで印刷するか尋ねる

If MsgBox("このシートを印刷しますか？", vbYesNo) = vbYes Then

' シートを印刷する

ws.PrintOut

End If

Next i

End Sub

●実行前～実行後　※プログラムを実行すると、指定されたシート（Sheet1とSheet2）の印刷プレビューが順番に表示されます。プレビューが表示された後、メッセージボックスが表示され、シートを印刷するかどうか尋ねます。 [はい] をクリックすると、シートが印刷されます。 [いいえ] をクリックすると、印刷せずに次のシートのプレビューに進みます。この処理がすべての指定されたシートに対して行われ、最後にコードが終了します。
（画面クリックして拡大）

Excel VBAワークブックをスッキリ整理！シート名順に並べ替える。ブック内のシートを整列（テクニック）

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、Excelのワークブック内のすべてのシートをシート名を元に並び替えるサンプルプログラムです。具体的には二重のForループを使用して、ワークブック内のすべてのシートを繰り返し処理します。最初のループは、i番目のシートを選択し、次のループは、i+1番目から最後のシートまでを選択します。この二重ループの中で、シート名がアルファベット順で正しい順序にない場合、シートの位置を移動して正しい順序に並び替えます。

並び替えの例：シート名、ＣＣＣ、ＢＢＢ、ＡＡＡ　⇒ＡＡＡ、ＢＢＢ、ＣＣＣに並び変わる。

【プログラムの流れ】
①　i番目のシートを選択する
②　i+1番目から最後のシートまで、j番目のシートを選択する
③　シートjの名前がシートiの名前よりもアルファベット順で前にある場合、シートjをシートiの前に移動する
④　ループを繰り返して、すべてのシートがアルファベット順になるまで並び替えを行う

【プログラム実行条件・注意事項】

①　このコードは大文字と小文字を区別せずに並び替えを行います。
②　シート名が漢字・記号の場合は、並び替え結果が予測できません。

'
'
Sub SortSheets()
    ' 変数宣言
    Dim i As Integer, j As Integer
    
    ' i番目のシートを選択するループ
    For i = 1 To ThisWorkbook.Sheets.Count
        ' j番目のシートを選択するループ (i+1番目から最後のシートまで)
        For j = i + 1 To ThisWorkbook.Sheets.Count
            ' シートjの名前がシートiの名前よりもアルファベット順で前にある場合
            If ThisWorkbook.Sheets(j).Name < ThisWorkbook.Sheets(i).Name Then
                ' シートjをシートiの前に移動する
                ThisWorkbook.Sheets(j).Move before:=ThisWorkbook.Sheets(i)
            End If
        Next j
    Next i
End Sub
'
'

Sub SortSheets()

' 変数宣言

Dim i As Integer, j As Integer

' i番目のシートを選択するループ

For i = 1 To ThisWorkbook.Sheets.Count

' j番目のシートを選択するループ (i+1番目から最後のシートまで)

For j = i + 1 To ThisWorkbook.Sheets.Count

' シートjの名前がシートiの名前よりもアルファベット順で前にある場合

If ThisWorkbook.Sheets(j).Name < ThisWorkbook.Sheets(i).Name Then

' シートjをシートiの前に移動する

ThisWorkbook.Sheets(j).Move before:=ThisWorkbook.Sheets(i)

End If

Next j

Next i

End Sub

●実行前～実行後　※プログラム実行後、Excelワークブック内のすべてのシートがシート名のアルファベット順に並び替えられます。例えば、シート名が “C”, “A”, “B” の順であった場合、コードの実行後、シート名は “A”, “B”, “C” の順に並び替えられます。これにより、シートを検索しやすくなり、ワークブックの整理が容易になります。
（画面クリックして拡大）

Excel VBA　一括置換！ワークブック内の全シートで指定文字列を置き換える方法

●プログラム説明　(サンプル③)

下記のプログラムは、まずユーザーに置き換える元の文字列と新しい文字列を入力してもらいます。その後、ワークブック内の全てのワークシートをループして、各セルに含まれる元の文字列を新しい文字列に置き換えます。。置き換える際に、大文字と小文字を区別せず、部分一致で検索を行います。ブック内の指定した文字列を一括で置き換える時に便利だと思います。

【プログラムの流れ】
①　元の文字列(OldText)を入力
②　新しい文字列(NewText)を入力
③　ワークブック内の全ワークシートに対してループ処理を行う
④　各ワークシートのセルで、元の文字列（OldText）をNewTextに置き換える

【プログラム実行条件・注意事項】
①　置き換えたい文字列がない場合でも、空白文字列を入力する必要があります。
②　MatchCase:=Falseのため、大文字と小文字は区別されません。
③　置き換え処理は一度実行すると元に戻せないため、処理前にデータのバックアップを取ることをお勧めします。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　SheetWork03（サンプルプログラム）

’
’
Sub FindAndReplace()
    ' ブック内の全てのワークシートの指定した文字列を置き換える
    
    ' Worksheet変数を宣言する
    Dim ws As Worksheet
    
    ' InputBoxで置き換える元の文字列を入力する
    Dim OldText As String
    OldText = InputBox("置き換える元の文字列を入力してください:", "元の文字列")
    
    ' InputBoxで置き換える文字列を入力する
    Dim NewText As String
    NewText = InputBox("置き換える新しい文字列を入力してください:", "新しい文字列")
    
    ' ワークブック内の各ワークシートをループする
    For Each ws In ThisWorkbook.Worksheets
        ' 現在のワークシートの各セルで"OldText"を"NewText"に置き換える
        ws.Cells.Replace What:=OldText, Replacement:=NewText, LookAt:=xlPart, MatchCase:=False
    Next ws
End Sub
’
’

’

Sub FindAndReplace()

' ブック内の全てのワークシートの指定した文字列を置き換える

' Worksheet変数を宣言する

Dim ws As Worksheet

' InputBoxで置き換える元の文字列を入力する

Dim OldText As String

OldText = InputBox("置き換える元の文字列を入力してください:", "元の文字列")

' InputBoxで置き換える文字列を入力する

Dim NewText As String

NewText = InputBox("置き換える新しい文字列を入力してください:", "新しい文字列")

' ワークブック内の各ワークシートをループする

For Each ws In ThisWorkbook.Worksheets

' 現在のワークシートの各セルで"OldText"を"NewText"に置き換える

ws.Cells.Replace What:=OldText, Replacement:=NewText, LookAt:=xlPart, MatchCase:=False

Next ws

End Sub

’

●実行前～実行後　※プログラム実行後、指定した元の文字列がワークブック内の全てのワークシートのセルから新しい文字列に置き換えられます。これにより、一括で文字列の変更ができます。今回は、「新宿支店」⇒「東京支店」へ置き換えられました。
（画面クリックして拡大）

Excel VBA　ワークシート間の登録されるデータの違いを見つける：シート間の比較：データの差分

●プログラム説明　(サンプル④)

今回説明するのは、Excelの2つのワークシート（Sheet1およびSheet2）を比較し、異なるセルをハイライトするサンプルプログラムです。ワークシート間で異なるセルデータを見つけ出す時に便利だと思います。それでは、順番に説明いたします。

【プログラムの流れ】

①　シートオブジェクト（ws1およびws2）を設定
②　Sheet1の使用中の範囲内でループを開始
③　Sheet2の対応するセルを設定
④　セルの値が異なるかどうかを確認
⑤　異なる場合、Sheet1の対応するセルを黄色でハイライト
⑥　ループを続ける

【プログラム実行条件・注意事項】
①　シート名が変更された場合、コードのシート名を適切に更新する必要があります。
②　セルの値のみを比較し、書式は比較しません。
③　このコードは、Sheet1とSheet2のセルが完全に一致している場合に限り、正確な結果が得られます。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　SheetWork04（サンプルプログラム）

Option Explicit

'
'
Sub CompareSheets()  'ワークシート間のデータをチェックする。
    ' シートオブジェクトを宣言
    Dim ws1 As Worksheet, ws2 As Worksheet
    ' セルの範囲を宣言
    Dim r1 As Range, r2 As Range, diffCell As Range

    ' シートオブジェクトを設定
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    ' Sheet1の使用中の範囲内でループ
    For Each r1 In ws1.UsedRange
        ' Sheet2の対応するセルを設定
        Set r2 = ws2.Cells(r1.Row, r1.Column)
        ' セルの値が異なるかどうかを確認
        If r1.Value <> r2.Value Then
            ' 異なる場合、Sheet1の対応するセルを黄色でハイライト
            Set diffCell = ws1.Cells(r1.Row, r1.Column)
            diffCell.Interior.Color = RGB(255, 255, 0)
        End If
    Next r1
End Sub
'
'

Option Explicit

Sub CompareSheets() 'ワークシート間のデータをチェックする。

' シートオブジェクトを宣言

Dim ws1 As Worksheet, ws2 As Worksheet

' セルの範囲を宣言

Dim r1 As Range, r2 As Range, diffCell As Range

' シートオブジェクトを設定

Set ws1 = Worksheets("Sheet1")

Set ws2 = Worksheets("Sheet2")

' Sheet1の使用中の範囲内でループ

For Each r1 In ws1.UsedRange

' Sheet2の対応するセルを設定

Set r2 = ws2.Cells(r1.Row, r1.Column)

' セルの値が異なるかどうかを確認

If r1.Value <> r2.Value Then

' 異なる場合、Sheet1の対応するセルを黄色でハイライト

Set diffCell = ws1.Cells(r1.Row, r1.Column)

diffCell.Interior.Color = RGB(255, 255, 0)

End If

Next r1

End Sub

●実行前～実行後　※プログラム実行後、Sheet1とSheet2のセルが比較され、値が異なる場合、Sheet1の該当セルが黄色でハイライトされます。これにより、ユーザーは簡単に異なるデータを特定し、必要に応じて修正できます。
（画面クリックして拡大）

Excel VBA　ワークブック内のシートを一括で保護・保護解除する方法

●プログラム説明　(サンプル⑤)

今回説明するのは、Excelのワークブック内のすべてのシートを保護または保護解除するためのものです。パスワードを使用してシートを保護し、保護されていない場合は保護し、保護されている場合は保護を解除するサンプルプログラムです。

【プログラムの流れ】

①　ワークブック内のすべてのシートに対してループを開始します。
②　各シートに対して以下の処理を行います:
③　シートが保護されていないかどうかを確認します。
④　シートが保護されていない場合は、指定されたパスワードを使用してシートを保護します。
⑤　シートが保護されている場合は、指定されたパスワードを使用して保護を解除します。
⑥　ループが終了すると、すべてのシートの保護または保護解除が完了します。

【プログラム実行条件・注意事項】

①　パスワードはコード内のPassword:=”password”の部分で指定されています。必要に応じて安全なパスワードに変更してください。
②　このコードは現在開いているワークブックのすべてのシートに対して操作を行います。他のワークブックに対して使用する場合は、コードを適切に修正する必要があります。
③　保護解除の際に正しいパスワードを指定しないとエラーが発生する可能性があります。パスワードを忘れた場合は、保護解除ができなくなるので注意が必要です。

Option Explicit

'
'
Sub ProtectAndUnprotectSheets()  'ワークシートを保護・解除
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ' シートが保護されていない場合
        If ws.ProtectContents = False Then
            ' 指定されたパスワードを使用してシートを保護
            ws.Protect Password:="password"
        Else
            ' シートが保護されている場合は保護を解除
            ws.Unprotect Password:="password"
        End If
    Next ws
End Sub

'
'

Option Explicit

Sub ProtectAndUnprotectSheets() 'ワークシートを保護・解除

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

' シートが保護されていない場合

If ws.ProtectContents = False Then

' 指定されたパスワードを使用してシートを保護

ws.Protect Password:="password"

Else

' シートが保護されている場合は保護を解除

ws.Unprotect Password:="password"

End If

Next ws

End Sub

●実行前～実行後　※プログラム実行後、現在のワークブック内のすべてのシートが処理されます。もしシートが保護されていない場合、指定されたパスワードを使用してシートが保護されます。もしシートが既に保護されている場合、指定されたパスワードを使用して保護が解除されます。処理が完了すると、すべてのシートの保護状態が変更されます。

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

VBAプログラム（テクニック）EXCEL, VBA