【ExcelVBA】シートごとにブックを保存するエクセルマクロ例

こんにちは！今回はエクセルマクロで一つのブック内のある複数のシートをそれぞれ別々のブックに保存してみようと思います。

エクセルマクロはとても便利な機能ですが、なかなか自分で作るとなるとハードルが高いものです。
ですが、一度作ってしまうと何度も使いまわしができるので非常に作業効率化になります。

今回のケースでは、月別などでシート管理しているようなエクセルの台帳を別々のブックにしたいときって結構あるなーと思ったので、それをテーマにマクロ化してみました。

このマクロを使ってもらえば、簡単かつスピーディにバラバラに保存することが出来ます。

※現在はマクロ経験者向けになっています。マクロの登録の仕方などは後でまとめたいと思います。

やりたいこと

・複数のシートで構成された１つのエクセルファイルをすべてシートごとに保存したい。

・マクロを実行して保存までやりたいので、シート名をファイル名にしてブック保存。

・一つだけ取り出すのではなく、全部バラバラに保存したい。

スクリプト例

Sub SplitSheets()
 
Dim i As Integer
Dim wb1, wb2 As Workbook
Dim Cnt As Integer
 
'１）シート数の取得
Set wb1 = ActiveWorkbook
Cnt = wb1.Sheets.Count

Dim CopyFileName

'EX）処理が止まらないようにダイヤログを抑制
Application.DisplayAlerts = False

For i = 1 To Cnt

'２）シート名でブックを作成
CopyFileName = ThisWorkbook.Path & "\" & wb1.Worksheets(i).Name & ".xlsx"
Workbooks.Add.SaveAs Filename:=CopyFileName

'３）元のブックのシートを先ほど作ったシート名のブックにコピー
Set wb2 = Workbooks(wb1.Worksheets(i).Name & ".xlsx")

wb1.Worksheets(i).Copy before:=wb2.Worksheets(1)
wb2.Worksheets(1).Name = wb1.Worksheets(i).Name
wb2.Worksheets("Sheet1").Delete
wb2.Close savechanges:=True

Next i

'EX）抑制したダイヤログ設定もとに戻す 
Application.DisplayAlerts = True

End Sub

このマクロをバラバラに保存したいエクセルのマクロに登録して実行してあげれば、そのエクセルファイルの場所にずらずらっとシート名でファイルを分割してくれます。

解説

主要な部分を抜粋して説明していきたいと思います。

１）シート数の取得

まずは今開いているブックにあるシート数を取得します。
シート数はワークブックオブジェクトのプロパティである「sheets.count」で取得できます。

Set wb1 = ActiveWorkbook　今開いているブックをwb1としてオブジェクト作成
Cnt = wb1.Sheets.Count　　Sheets.Countプロパティを呼び出してシート数を取得

これによって処理しなくてはならない回数が分かります。

２）シート名でブックを作成

別のブックとして保存するには、「Workbooks.Add.SaveAs」メソッドを使うことで実現できます。名前を付けて保存の機能になります。
※この時作成されるのは新規ブックのため、「Sheet1」だけがあるだけのブックです。

名前を付けて保存というくらいですから、付けたい名前が必要になります。ここでは元のシート名が欲しいので、WorksheetsオブジェクトのNameプロパティで取得しています。

ThisWorkbook.Path & "\" & wb1.Worksheets(i).Name & ".xlsx"
ThisWorkbook.Path　今開いているブックの場所を取得
wb1.Worksheets(i).Name　i番目のシート名を取得

今回では、元のブックと同じ場所に保存したいので、「ThisWorkbook.Path」を使用しています。
そのため、ブック名とシート名が同じだと失敗するので注意してください。

直前のFor分でシートの数だけ繰り返すという処理を記述しているので、１～シートの最大枚数まで１ずつ「i」を足していきながら処理します。このときの「i」はシートのインデックス番号としての役割を果たします。

エクセルのシートはインデックス番号というもので管理されていて、1ページ目、2ページ目・・・といった具合で番号でもシートを指定できます。

そのため、「i」が１の時は１番目のシートの「.Name」を取得できるわけですね。
これを全シート分繰り返すため、それぞれシートごとにブックが作成されます。

３）元のブックのシートを先ほど作ったシート名のブックにコピー

次に作成したブックにシートをコピーしていきます。
まずは作ったブックを扱いやすいように「wb2」に取得します。原理は先ほどと同じくインデックス番号を使った方法で行います。

Set wb2 = Workbooks(wb1.Worksheets(i).Name & ".xlsx")

次にシートをコピーしていきます。「wb1」は元のブック、「wb2」はコピー先のブックになります。

wb1.Worksheets(i).Copy before:=wb2.Worksheets(1)

コピー先のブックに対して先頭にあるシートの前にコピーするように指定しています。
この場合は「.copy」に「before:」オプションを付けてあげます。この際の「Worksheets(1)」は先ほどのインデックス番号でいう一番左にあるシートの前ということなので、必然と先頭にコピーされます。

wb2.Worksheets("Sheet1").Delete
wb2.Close savechanges:=True

最後にもとからあった「Sheet1」という名前」のついたシートを削除して保存して完了です。

この後「i」を１増やして最後のシートまで繰り返していきます。
これによってすべてのシートが別のブックに保存されていきます。

EX）処理が止まらないようにダイヤログを抑制

「Application.DisplayAlerts」は警告などダイヤログ表示を抑制する設定値です。

これが「True（有効）」のままですと、1ページ保存するごとに警告が表示されてしまいます。
すると毎回クリックが必要になってしまうため、今回は最後まで自動で行ってほしいので「False（無効）」に設定しました。

あとがき

今回は複数のシートで構成されたエクセルブックをシートごとにバラバラに保存するマクロを紹介させていただきました。

月別に管理していた台帳や、機種別にシート分けしたチェックシートなどいろんな場面でシート分けしたブックはでてきます。自分が作ったものじゃない場合、これを分解するのはつらいですよね。

SEの仕事をすると、意外にこの作業をやるケースは多いです。しかも手動でやると結構面倒です。
管理台帳なんかは大体このケースですし、なぜかピンポイントのシートをお客さんに出さねばならなかったり・・・。
やっぱりこういう繰り返し作業はマクロでやらせるのがベストでしょう。

ざっくりとした説明でしたので、わかりにくい部分もあったかもしれません・・・。
もし質問事項があればコメントしてくださいね♪

何かのお役に立てれば幸いです。