VBA｜複数のシートをコピーしてから新規ブックとして保存
本記事はワークシートをコピーするという事例です。ポイントは3つです。
- 複数のシートを別ブックにコピーしたい
- コピー先のブックではコピー元の表示（印刷範囲など）を維持したい
- 作成した別ブックを保存したい
「複数シート」や「別ブック」など要素がたくさん登場するので一見難しそうです。
単純に考えると以下のような作業が待っており初心者には難しそうに見えてしまいます。
- 複数のシートという事は繰り返し処理を使おうかな・・・
- 新規ブックを用意する際は元データの印刷範囲などの各種設定は引き継げないよね・・・
- 作成した別ブックをコピー元と同じ場所に保存したい・・・
という様に考えることがたくさんありますができるだけ簡単かつ少ないコードで作業を構築してみます。
比較的少ないコード量で仕事を再現してみました。
作業内容は少々難しいのですが解説をしっかり入れています。
関連書籍
今回の事例はコード量としては少ないのですが色々な要素を組み合わせてコードを用意します。
事例集を辞書代わりに使ってコードを組み合わせるという作業が必要になります。
事例集の良いところは「やりたい事起点でコードが検索できる」ということです。
どの本でも良いので1冊用意しておくと作業をスムーズに進める事ができる様になりますよ。
おすすめこちらの本は電子書籍もおすすめです。Kindleストアのリンクを用意しておきます。
関連記事
ワークシート内の値をコピペする方法を紹介しています。
PasteSpecialメソッドの使い方VBA×コピペ｜値のみ貼り付け＆簡単高速にコピーできる方法を公開
事例を紹介
冒頭で提示した課題を達成するために事例を用意しました。
1つの.xlsmデータからワークシート3枚をコピーして別ブックに展開後にデータを保存するという事例です。
作業前のデスクトップとワークシート
デスクトップには.xlsmのデータが1つ用意されています。今回使用するデータです。
データ名は「シートの移動（コピー）.xlsm」という名前です。
「シートの移動（コピー）.xlsm」は3枚のワークシートを持っています。
各ワークシートには画像を用意しておきました。
作業後のデスクトップとワークシート
作業後のデスクトップです。ワークブックが1つ増えました。シートのコピーの為に用意された「sample.xlsx」です。
このデータは「シートの移動（コピー）.xlsm」と同じ環境に用意される仕様にしています。
作業後のワークシートです。「sample.xlsx」は「シートの移動（コピー）.xlsm」と全く同じデータを持っています。
シートの配置や画像の大きさなど全く同じ仕様でデータを用意することができます。
コード
ではここからは事例のようにデータを用意する為のコードを紹介します。
以下コードは「シートの移動（コピー）.xlsm」の標準モジュールに用意されたコードになります。
Sub ワークシート自体をコピーする()
Dim ws1 As String
ws1 = Worksheets("Sheet1").Name
Dim ws2 As String
ws2 = Worksheets("Sheet2").Name
Dim ws3 As String
ws3 = Worksheets("Sheet3").Name
Worksheets(Array(ws1, ws2, ws3)).Copy
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
With wb1
Application.DisplayAlerts = False
.SaveAs Filename:=ThisWorkbook.Path & "\" & "sample.xlsx"
.Close
Application.DisplayAlerts = True
End With
End Sub半分ぐらいは変数の定義です。繰り返し処理は使ってないし長文のコードも無いです。
コード量が少ないので一見簡単そうに見えますがいくつかポイントがあります。
解説
課題を解決させるためのキーとなる要素はこちらです。
- 変数とオブジェクト変数
- WorksheetsオブジェクトのCopyメソッド
- Array関数
- WorkbookオブジェクトのSaveAsメソッド
1つずつ説明していきます。
少々難しいところもありますが何回か読んで理解してください。
1_変数とオブジェクト変数
紹介したコードの中で変数、オブジェクト変数は以下箇条書きのような使われ方をしています。
- 変数 ・・・ ワークシート名を格納する為に使用
- オブジェクト変数 ・・・ sample.xlsxのもとになるワークブックを格納する為に使用
基本的には変数もオブジェクト変数も機能は同じです。変数というのは値を一時的に格納する為の領域です。
変数とはVBA-8変数の宣言とは何かマクロの中で使い方を決めます
実際にコードを書くにあたり2つの要素の違いとなるのはSetステートメントの有無です。
オブジェクト変数には「Set」が必要です。詳細はこちらの記事で解説しています。
オブジェクト変数とはVBAで転記｜ブック間で文字列をコピペするマクロの作り方を教えます
2_WorksheetsオブジェクトのCopyメソッド
このコードがキーとなる要素の中で最大のポイントになります。2つに分解して説明します。
- WorksheetsオブジェクトのCopyメソッド
- ワークシートの名前をArray関数で用意している
2_1_WorksheetsオブジェクトのCopyメソッド
Copyメソッドはいろいろなオブジェクトに紐付いています。
名前は同じでも上位オブジェクトによって効果が異なりますのでご注意ください。
今回はWorksheetsオブジェクトのCopyメソッドです。どんなことができるのか手作業に置き換えて説明してみます。
手作業でコードを解説
ワークシートタブの上で右クリックした後に表示される要素から「移動またはコピー」をクリックします。
すると「シートの移動またはコピー」というダイアログボックスが表示されます。
ここで移動先ブック名を（新しいブック）にして左下の「コピーを作成する」にレ点を入れます。
そうするとワークシートの完全なコピーをしつつ新しいブックを生成します。
この一連の操作と同じことができるのがWorksheetsオブジェクトのCopyメソッドです。
2_2_ワークシートの名前をArray関数で用意している
WorksheetsオブジェクトのCopyメソッドにはパラメータとして複数シートを選択（指定）することができます。
今回は配列でシート名を与えるという方法をとります。このあと紹介するArray関数を使って説明します。
3_Array関数
この関数はひと言で表すと「配列」です。Microsoftのドキュメントからデータを引用します。
構文
Array(arglist)
パラメータ
MiMicrosoftドキュメント：Array 関数から抜粋、加工引数 arglist は必須です。コンマ区切りのリストを用意することになります。
型はバリアント型 (Variant)です。
かっこの中にパラメータとして配列に格納したい値を用意します。
格納したい値が複数ある時はコンマ（カンマ）で区切りましょう。
今回のコードの中ではワークシートの名前が格納された変数をコンマ（カンマ）区切りで並べています。
参考：配列を理解する為の事例を用意しました
配列へ値を格納してから取り出すという一連の行為を見る為に簡単なコードを用意しました。
おすすめはステップインで1行ずつコードを実行しながらローカルウインドウの値を確認することです。
非常に地道な作業ですがどうやって値が動いているのかを理解するにはこの方法が一番です。
Sub Array関数()
Dim ary As Variant
Dim i As Long
ary = Array(10, 20, 30)
Stop
For i = 0 To 2
Debug.Print ary(i)
Next
End Subこのコードでは前半で配列ary(0)に「10」を格納、配列ary(1)に「20」を格納、配列ary(2)に「30」を格納しています。
これで配列aryには3つの部屋が出来て3つの値を管理することができるようになりました。
その後格納した値をイミディエイトウィンドウに順番に表示させるという事をしています。
結果画像のようにイミディエイトウィンドウに配列の値が表示されます。
このように配列は値を集合or単体で持つことができる非常に便利な要素です。
配列に興味をもった方はこちらの記事をご覧ください。より応用の利くコードを用意しています。
配列を使ってみる【一次元配列のコード&解説】文字列を作る×配列に格納する×出力する
4_WorkbookオブジェクトのSaveAsメソッド
最後はデータの保存です。SaveAsメソッドを使用します。
SaveAsメソッドを使えばデータの保存場所やデータ名も好きなように決める事ができます。
詳細はこちらの記事で解説しています。
SaveAsメソッドについて解説VBAはフォルダ内の複数ブック×全部のシート名をセットで取得できる
SaveAsメソッドと一緒に使うコードの中にもポイントとしておさえておきたい要素が2つありますので紹介します。
4_1_ThisworkbookオブジェクトのPathメソッド
このコードによってコピー元データとコピー先データは同じ環境に保存されることになります。
Pathメソッドについての記事はこちらをご覧ください。
Pathメソッドについて【VBA】絶対パス相対パスの書き方｜理解はカレントディレクトリがポイント
4_2_ApplicationオブジェクトのDisplayAlertsプロパティ
このプロパティに値を設定することでアラート用のダイアログボックスを非表示にします。
非表示にする理由は作業を省略したいからです。もう少し具体的に言いますと「表示を消すのが面倒だから」です。
具体的な使い方
プロパティに入る値はBoolean型なのでTrueかFalseを設定することになります。
デフォルトではTrueが入っているのであえて警告メッセージを出したい時は何も設定する必要は無いです。
Falseでアラート用のダイアログボックス（警告メッセージなど）を非表示にします。
詳細はMicrosoftのドキュメントをご覧ください。
DisplayAlertsプロパティのFalse設定は作業が終わり次第Trueに戻すことをおすすめします。
設定をもとに戻さないと他の作業でもアラート用のダイアログボックスが出なくなってしまいます。
まとめ
複数のシートをコピーしてから新規ブックとして保存する方法を紹介しました。
ポイントはワークシートごとそのまま別ブックにコピーするという事です。
この作業をWorksheetsオブジェクトのCopyメソッドが担当しているという内容でした。
参考：今回のコードを使うことによるメリット
今回の様なブックを丸ごとコピーするという方法はいくつかのメリットがあります。
一例を挙げてみます。ワークシートを作ってから値だけ貼り付けるという作業を想定してください。
この様な作業をするとコピー元のExcelで持っている設定がコピー先のExcelの設定に引き継がれない事が多々あります。
具体的には「PCやExcelの表示倍率による印刷範囲のズレ」などです。他にも細かい不具合が出る事があります。
そんな中今回のコードは「印刷範囲や各種設定を残したまま別ブックにワークシートをコピーする」ことができます。
よって書式の変化や印刷範囲のズレなどの不具合が非常に起きにくいです。メリットの具体例を紹介しました。