トップ > VBA・VBS > Excel VBA > シートごとに別ブックとして分割保存する

シートごとに別ブックとして分割保存する

Excel VBA(担当者別ファイルを一括で作る)

担当者別にシートを分けた後、今度は「シートごとにファイルにして渡す」作業が待っています。シートをコピーして、新規ブックに貼って、名前を付けて保存、を人数分。このレシピでは、全シートを「シート名.xlsx」として一括で別ファイルに保存するマクロを作ります。「条件でデータを別シートに振り分ける」と組み合わせると、振り分け→ファイル化まで一気に行けます。

✅ これができます:ボタン1つで、各シートを別々のExcelファイルとして一括保存できるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)は担当者別のシートが入った1つのブック。実行すると(右)、シート名のExcelファイルが保存先フォルダに一括でできます

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 分割したファイルの保存先フォルダ
Const OUTPUT_FOLDER As String = "C:\仕事\分割保存"
' ② 分割しないシート名(, 区切り)
Const EXCLUDE_SHEETS As String = "設定,集計"
' ===== ここから下は変えなくてOK =====
 
Sub シートごとに分割保存する()
    Dim fso As Object
    Dim ws As Worksheet
    Dim newBook As Workbook
    Dim path As String, cnt As Long
    Dim saveOk As Boolean
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(OUTPUT_FOLDER) Then
        MkDir OUTPUT_FOLDER
    End If
 
    cnt = 0
    For Each ws In ThisWorkbook.Worksheets
        If Not 除外シートか(ws.Name) Then
            ws.Copy
            Set newBook = ActiveWorkbook
            path = OUTPUT_FOLDER & "\" & ws.Name & ".xlsx"
            Application.DisplayAlerts = False
            Err.Clear
            On Error Resume Next
            newBook.SaveAs fileName:=path, FileFormat:=xlOpenXMLWorkbook
            saveOk = (Err.Number = 0)
            On Error GoTo 0
            Application.DisplayAlerts = True
            newBook.Close SaveChanges:=False
            If saveOk Then cnt = cnt + 1
        End If
    Next ws
    MsgBox cnt & " 件のファイルに分割保存しました。" & vbCrLf & OUTPUT_FOLDER, vbInformation, "分割保存"
End Sub
 
Private Function 除外シートか(ByVal sheetName As String) As Boolean
    Dim parts As Variant, p As Variant
    除外シートか = False
    parts = Split(EXCLUDE_SHEETS, ",")
    For Each p In parts
        If Trim(CStr(p)) = sheetName Then
            除外シートか = True
            Exit Function
        End If
    Next p
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの2か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1OUTPUT_FOLDER分割したファイルの保存先フォルダ自分のPCの場所に書き換えます。フォルダが無ければ自動で作ります(親フォルダは先に存在している必要があります)
2EXCLUDE_SHEETS分割しないシート名(カンマ区切り)元データや集計シートなど、配布対象でないシートを書きます

組み込み手順

  1. 分割したいブックで、シート名がそのままファイル名になることを確認します(ファイル名に使えない文字がシート名に無いことも合わせて確認)
  2. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  3. このページのコードを貼り付け、設定エリア①②を書き換えます
  4. ファイルを .xlsm で保存し、マクロを実行します(ボタン登録が便利です)
  5. 保存先フォルダにシート名の .xlsx が並んでいれば成功です

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

同じ名前のファイルがある場合は、確認なしで上書きされます。再実行すれば配布用ファイル一式が最新化されます。分割されたファイルは .xlsx 形式なので、元のブックのマクロ(標準モジュール)は含まれません。値と書式はそのまま移ります。

カスタマイズ例

ファイル名に日付や担当コードを付けたい

path の行でファイル名を組み立てています。ws.Name の前後に、Format(Date, "yyyymmdd") & "_" のような文字列を足せます。

元の形式(.xlsm)のまま分割したい

シート単体のコピーでは標準モジュールは引き継がれないため、マクロつきのままの分割は作りが変わります。配布先でマクロが要らない業務なら、このレシピの .xlsx 分割で十分なことがほとんどです。

振り分けから分割まで一気に

「条件でデータを別シートに振り分ける」のマクロの最後に、このマクロを呼び出す1行を足すと、振り分け→ファイル化までが1クリックになります。

つまずきどころ

関連レシピ

次はこれ:Outlookでメール下書きを一括作成する
ファイルが用意できたら、次は宛先ごとのメール作成まで自動化しましょう

よくある質問

Q. 分割先のファイルにもマクロは入る?

A. 入りません。.xlsx 形式で保存するため、分割先はマクロなしの配布用ファイルになります。これが安全面でも利点です(配布先で警告が出ません)。

Q. シートが100枚あっても大丈夫?

A. 1枚ずつコピーと保存を繰り返す作りなので、枚数に比例して時間がかかります。数十枚なら実用範囲です。

Q. PDFで配るのとどっちがいい?

A. 相手が値を編集して返す業務ならExcelファイル、閲覧と保管が目的ならPDF(「全シートをPDFで一括出力する」)が向いています。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る