[Excel VBA] ブックの各シートをひとまとめにするVBAコード

1. 1つのシートにまとめるマクロを作った

大きな名簿などは、シートを分けて管理することがあります。
しかし、差し込み印刷などでは1つのシートにまとめる必要があります。

そこで、エクセルブックを開いて各シートの内容をまとめるマクロブックを作りました。

1-1. VBAコード

マクロを実行すると、指定したブックの全シートのデータがマクロブックのシート1にコピーされます。

Sub CombineSheets()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim destWs As Worksheet
    Dim lastRow As Long
    Dim fileName As String
    Dim isFirstSheet As Boolean
    
    ' ファイル選択ダイアログを表示
    fileName = Application.GetOpenFilename("Excel ファイル (*.xlsx), *.xlsx", , "コピーするブックを選択してください")
    
    ' キャンセルされた場合は終了
    If fileName = "False" Then Exit Sub
    
    ' 選択されたブックを開く
    Set wb = Workbooks.Open(fileName, ReadOnly:=True)
    
    ' マクロブックのシート1を取得
    Set destWs = ThisWorkbook.Sheets(1)
    
    ' マクロブックのシート1をクリア
    destWs.Cells.Clear
    
    isFirstSheet = True
    lastRow = 1
    
    ' 各シートのデータをコピー
    For Each ws In wb.Worksheets
        If isFirstSheet Then
            ' 1つ目のシートは全行コピー
            ws.UsedRange.Copy destWs.Cells(lastRow, 1)
            lastRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1
            isFirstSheet = False
        Else
            ' 2つ目以降のシートは2行目からコピー
            If ws.UsedRange.Rows.Count > 1 Then
                ws.UsedRange.Offset(1).Resize(ws.UsedRange.Rows.Count - 1).Copy destWs.Cells(lastRow, 1)
                lastRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1
            End If
        End If
    Next ws
    
    ' 元のブックを閉じる
    wb.Close SaveChanges:=False
    
    MsgBox "全シートのデータがコピーされました。", vbInformation
End Sub

元のブックは読み取り専用で開き、結果は、マクロブックのシート1に出力します。
各シートの1行目は見出しとして、1つ目のシート以外は除外します。

2. 「マクロ有効ブック」の作り方

マクロを含むブックは、コードを追加して.xlsmの形式で保存します。

まず、Excelで空白のブックを新規作成します。
Altキーを押しながらF11を押すと、Visual Basic Editorが開きます。

コードを入力したら、「名前をつけて保存」します。
ファイルの種類から「Excel マクロ有効ブック（*.xlsm）」を選択します。

2-1. マクロ有効ブックの中のマクロを実行する

あとは、必要なときにマクロを実行します。

マクロ実行すると、ファイル選択画面があり、指定したブックを読み込みます。

3. 【応用】処理速度を上げるには途中の画面更新を止める

表示の更新を抑制することで処理を大幅に効率化できます。
これは特に大量のデータを扱う場合に有効です。

    ' パフォーマンス最適化の設定
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

ただし、処理中は画面が更新されないため、ユーザーには何も表示されません。

Sub CombineSheets()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim destWs As Worksheet
    Dim lastRow As Long
    Dim fileName As String
    Dim isFirstSheet As Boolean
    Dim startTime As Double
    
    ' 処理時間の計測開始
    startTime = Timer
    
    ' ファイル選択ダイアログを表示
    fileName = Application.GetOpenFilename("Excel ファイル (*.xlsx), *.xlsx", , "コピーするブックを選択してください")
    
    ' キャンセルされた場合は終了
    If fileName = "False" Then Exit Sub
    
    ' パフォーマンス最適化の設定
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    ' 選択されたブックを読み取り専用で開く
    Set wb = Workbooks.Open(fileName, ReadOnly:=True)
    
    ' マクロブックのシート1を取得
    Set destWs = ThisWorkbook.Sheets(1)
    
    ' マクロブックのシート1をクリア
    destWs.Cells.Clear
    
    isFirstSheet = True
    lastRow = 1
    
    ' 各シートのデータをコピー
    For Each ws In wb.Worksheets
        If isFirstSheet Then
            ' 1つ目のシートは全行コピー
            ws.UsedRange.Copy destWs.Cells(lastRow, 1)
            lastRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1
            isFirstSheet = False
        Else
            ' 2つ目以降のシートは2行目からコピー
            If ws.UsedRange.Rows.Count > 1 Then
                ws.UsedRange.Offset(1).Resize(ws.UsedRange.Rows.Count - 1).Copy destWs.Cells(lastRow, 1)
                lastRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1
            End If
        End If
    Next ws
    
    ' 元のブックを閉じる
    wb.Close SaveChanges:=False
    
    ' パフォーマンス最適化の設定を元に戻す
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ' 処理時間の計算
    Dim elapsedTime As Double
    elapsedTime = Timer - startTime
    
    MsgBox "全シートのデータがコピーされました。" & vbNewLine & _
           "処理時間: " & Format(elapsedTime, "0.00") & " 秒", vbInformation
End Sub

こちらもどうぞ。

データベースとしてのExcelの表の作り方（テーブルとフィールド）

Excelでデータを効率的に管理するには、データベースの視点が重要です。1つのシートには1つの表を作成し、1行に1つの項目を記録するようにします。必要に応じてテーブルを増やしたり、フィールドを追加したりして、データの整理と構造化を行います。

chiilabo.com

[Word] 差込印刷で宛名データをチェックで絞り込んで修正したい

差込印刷を使用する際に、宛名データをチェックボックスで絞り込むことができます。 「差し込み印刷の宛先」ダイアログボックスを表示する 必要な宛先にチェックを入れる 「結果のプレビュー」で確認する 個別に編集できるWord文書にする 宛名シールの一部を印刷し直したいときなんかに便利だね。 「差し込み印刷の宛先」を表示 Wordの差込文書を用意します。「差し込み文書」タブの「アドレス帳の編集」をクリックします。「差し込み印刷の宛先」ダイアログボックスが表示されます。 チェック欄で宛...

chiilabo.com

[Excel] 下書きと清書のシートに分ける（リンク貼り付け）

エクセルでデータ管理を効率化するために、集計用と表示用のシートを分けます。データ入力用シートには全ての情報を入力し、報告書用シートでは必要な情報を整理して表示します。リンク貼り付け機能を使用することで、データの自動更新や作業分担が容易になり、ミスも減少します。

chiilabo.com

[Excel]個人用マクロブックがロックされないように「読み取り専用」にするマクロのメリット・デメリット（ChangeFileAccess, xlReadOnly）

Excelの個人用マクロブックは自動読み込み時に排他制御の問題が発生し、警告メッセージが表示されることがあります。 この問題を解決するために、マクロブックを読み取り専用モードで開くか、自動読み込みを無効にする方法があります。 ただし、これらの解決策にはマクロの編集や利用に関して一定の制限があります。 この警告ってわずらわしいよね。 警告メッセージの原因（自動読み込みと排他制御） Excelを開いて個人用マクロブックが開かれているときに、エクスプローラーのプレビューで表示したり...

chiilabo.com

QRコードを読み込むと、関連記事を確認できます。