Excel VBAで別のブック（ファイル）へシートをコピー・移動する方法

Excel VBAで別ブックのシートをコピーする方法をお探しですね。

広告

Excel VBAで別のブックへシートをコピー・移動する方法【実務で使える完全ガイド】

Excel VBAで別のブックへシートをコピー・移動したい場面って、実務では本当によくあります。

たとえば、月次報告書から集計用ブックへシートを集めたり、テンプレートシートを別ファイルへ配布したり、外部ファイルのデータシートをマクロ実行ブックへ取り込んだり。

手作業なら「シートの移動またはコピー」メニューでサクッとできますが、VBAで自動化しようとすると意外とつまずきます。

ブックの指定があいまいだと、「あれ？どこにコピーされた？」となったり、コピー後のシートを編集しようとしてエラーになったり。

この記事では、そんな実務でよくあるトラブルを避けながら、別のブックへシートをコピー・移動する方法を基本から丁寧に解説していきます。

Excel VBAで別のブックへシートをコピーする基本

Excel VBAでワークシートを別のブックへコピーするには、Worksheetオブジェクトの`Copy`メソッドを使います。

ポイントは、コピー元のシートとコピー先のブックをそれぞれ変数に入れて、`Before`（～の前）または`After`（～の後ろ）で挿入位置を指定することです。

コピー先のブックがすでに開いている場合は、`Workbooks(“ファイル名.xlsx”)`で参照できます。

“`vba
Sub 別ブックへシートをコピーする()

Dim srcWb As Workbook
Dim dstWb As Workbook
Dim srcWs As Worksheet

Set srcWb = ThisWorkbook
Set dstWb = Workbooks(“集計用ブック.xlsx”)
Set srcWs = srcWb.Worksheets(“データ”)

srcWs.Copy After:=dstWb.Worksheets(dstWb.Worksheets.Count)

End Sub
“`

このコードでは、マクロが入っているブックの「データ」シートを、「集計用ブック.xlsx」の一番後ろへコピーしています。

`dstWb.Worksheets(dstWb.Worksheets.Count)`は、コピー先ブックの最後のシートという意味です。

別ブックの末尾に追加する処理は実務でよく使うので、この書き方を覚えておくと便利ですよ。

先頭にコピーしたいなら`Before:=dstWb.Worksheets(1)`、特定のシートの後ろに入れたいなら`After:=dstWb.Worksheets(“概要”)`のように指定できます。

コピー先のブックが閉じている場合は、先に`Workbooks.Open`で開いてからコピーします。

ファイルを開くとそのブックがアクティブになることもありますが、処理対象を変数でちゃんと指定していれば、アクティブブックに左右されにくくなります。

VBAを始めたばかりの頃は`ActiveWorkbook`や`ActiveSheet`を使いたくなるんですが、複数のブックを扱う処理では誤動作の原因になりやすいんです。

「どのブックにコピーされたのか分からない」というトラブルは、だいたいこれが原因です。

“`vba
Sub 閉じているブックへシートをコピーする()

Dim srcWb As Workbook
Dim dstWb As Workbook
Dim srcWs As Worksheet
Dim dstPath As String

Set srcWb = ThisWorkbook
Set srcWs = srcWb.Worksheets(“データ”)

dstPath = “C:\Users\example\集計用ブック.xlsx”
Set dstWb = Workbooks.Open(dstPath)

srcWs.Copy After:=dstWb.Worksheets(dstWb.Worksheets.Count)

dstWb.Save
dstWb.Close

End Sub
“`

このように、別ファイルへシートをコピーする処理では「コピー元」「コピー先」「挿入位置」を明確に分けて考えると理解しやすくなります。

コピー先のブックを開いたままにするのか、保存して閉じるのかも実務では重要です。

自動処理として使う場合は、コピー後に`Save`するかどうかを必ず決めておきましょう。

別のブックへシートを移動する方法とCopyとの違い

シートをコピーではなく移動したい場合は、`Copy`メソッドの代わりに`Move`メソッドを使います。

書き方はほぼ同じで、`Before`または`After`に移動先の位置を指定します。

ただし、コピーと移動には大きな違いがあります。

`Copy`は元のブックにシートが残りますが、`Move`は元のブックからシートがなくなります。

そのため、移動後に元のシート変数を使い続けると、参照先が無効になってエラーになることがあるので注意が必要です。

“`vba
Sub 別ブックへシートを移動する()

Dim srcWb As Workbook
Dim dstWb As Workbook
Dim moveWs As Worksheet

Set srcWb = ThisWorkbook
Set dstWb = Workbooks(“集計用ブック.xlsx”)
Set moveWs = srcWb.Worksheets(“データ”)

moveWs.Move After:=dstWb.Worksheets(dstWb.Worksheets.Count)

End Sub
“`

移動処理では、同じシートに対して複数回`Move`を実行することはできません。

最初の移動が完了した時点で、そのシートは元のブックから消えているからです。

サンプルコードを試すときに、先頭へ移動、途中へ移動、末尾へ移動といったコードを連続して書くと、途中でオートメーションエラーが発生する場合があります。

これはVBAの文法ミスというより、すでに存在しないシートを操作しようとしていることが原因なんです。

コピーと移動の使い分けは、元ファイルを残したいかどうかで判断します。

原本として残す必要がある報告書やテンプレートは`Copy`が安全です。

一方、処理済みシートを別ブックへ退避して元ブックを軽くしたい場合や、シート構成を整理したい場合は`Move`が向いています。

ただし、元ブックに最低限必要なシートがなくなると、後続処理や保存時の運用に影響する可能性があります。

特に、マクロ内で`ThisWorkbook.Worksheets(“データ”)`を後から参照している場合は、移動後にはその名前のシートが存在しない点に注意してください。

また、コピーや移動の対象ブックにシート名の重複がある場合、Excel側で「データ (2)」のような名前に自動変更されることがあります。

コピー後に特定のシート名で処理を続けたい場合は、コピー直後に変数へ入れて名前を設定し直すのが安全です。

シート名には使用できない文字や31文字以内という制限もあるので、外部データからシート名を作る場合は事前に整形しておくとトラブルを減らせます。

別ファイルのシートをマクロ実行ブックへ取り込む実践例

実務では、「マクロが入っているブックから別ファイルへコピーする」だけでなく、「別ファイルにあるシートを、マクロを実行しているブックへ取り込みたい」というケースも多くあります。

この場合に重要なのが、マクロ実行ブックを`ThisWorkbook`として最初に変数へ入れておくことです。

`Workbooks.Open`で外部ファイルを開くと、その外部ファイルがアクティブになることがあります。

そこで`ActiveWorkbook`に頼ると、どちらのブックを操作しているのか分からなくなりやすいんです。

“`vba
Sub 別ファイルのシートを取り込む()

Dim macroWb As Workbook
Dim importWb As Workbook
Dim importPath As String

Set macroWb = ThisWorkbook

importPath = “C:\Users\example\インポート元.xlsx”
Set importWb = Workbooks.Open(importPath)

importWb.Worksheets(“データ”).Copy After:=macroWb.Worksheets(“概要”)

importWb.Close SaveChanges:=False

End Sub
“`

このコードでは、「インポート元.xlsx」の「データ」シートを、マクロ実行ブックの「概要」シートの後ろへコピーしています。

外部ファイルは読み取り元として使うだけなので、最後に`SaveChanges:=False`で保存せずに閉じています。

これで、誤ってインポート元ファイルを変更して保存してしまうリスクを避けられます。

取り込み処理では、外部ファイルを開く前にマクロ実行ブックを変数へ入れておくことが、安定したコードを書くための基本です。

コピー位置は、ブックではなくワークシートを直接指定しても構いません。

たとえば、マクロ実行ブックの「勤務表」シートの前に取り込みたい場合は、`Before:=macroWb.Worksheets(“勤務表”)`のように書けます。

`Before`と`After`はコピー先の位置を決める引数なので、コピー元とは別のブックのシートを指定しても問題ありません。

つまり、コピー元が外部ファイル、コピー先がマクロ実行ブックであっても、正しくオブジェクトを指定していれば同じ考え方で処理できます。

この処理を応用すると、フォルダ内の複数ファイルから特定シートだけを集約する、毎月の帳票ファイルから「明細」シートを取り込む、といった自動化にもつなげられます。

その際は、ファイルを開く→シートをコピーする→コピー後のシート名を変更する→元ファイルを閉じる、という流れを1ファイルずつ丁寧に処理します。

複数ブックを扱うVBAでは、ブック名やシート名を省略しないことが安定性を高める最も重要なポイントです。

コピー・移動後のシートを編集する方法とエラー対策

`Copy`や`Move`を実行した後、そのコピー先または移動先のシートを編集したい場合がよくあります。

たとえば、コピーしたシート名を変更したり、A1セルに処理日を入力したりするケースです。

ただし、`Worksheet.Copy`や`Worksheet.Move`は、新しく作成されたシートを戻り値として返してくれません。

つまり、`Set newWs = srcWs.Copy(…)`のような書き方はできないんです。

そのため、コピー直後にアクティブになったシートを変数へ入れてから操作するのが一般的です。

“`vba
Sub コピー後のシートを編集する()

Dim srcWs As Worksheet
Dim dstWb As Workbook
Dim newWs As Worksheet

Set srcWs = ThisWorkbook.Worksheets(“テンプレート”)
Set dstWb = Workbooks(“集計用ブック.xlsx”)

srcWs.Copy After:=dstWb.Worksheets(dstWb.Worksheets.Count)

Set newWs = ActiveSheet

newWs.Name = “当月データ”
newWs.Range(“A1”).Value = Date

End Sub
“`

ここで重要なのは、`ActiveSheet.Range(“A1”)`のように直接処理を書き続けないことです。

コピー直後はたしかにコピーされたシートがアクティブになりやすいですが、処理が複雑になると途中でアクティブシートが変わる可能性があります。

そのため、コピー直後に`Set newWs = ActiveSheet`として変数に入れ、その後は`newWs.Range(“A1”)`のように親シートを明示して操作します。

これで、意図しないシートへ値を書き込むミスを防げます。

エラー対策としては、コピー先ブックが開いているか、指定したシート名が存在するか、ブックの構成が保護されていないかを確認することが大切です。

ブックの構成保護が有効になっていると、シートの追加・移動・削除ができずエラーになる場合があります。

また、コピー先が読み取り専用で開かれている場合は、コピー自体はできても保存できないことがあります。

業務用マクロでは、処理前に対象ファイルの存在確認や、処理後の保存可否を考慮しておくと安心です。

実務で特に押さえておきたい注意点は、次のとおりです。

– `ThisWorkbook`はマクロが保存されているブック、`ActiveWorkbook`は現在アクティブなブックを指します
– 別ブックへコピー・移動する場合は、コピー元とコピー先をWorkbook変数に入れると安全です
– `Move`は元シートが消えるので、移動後に同じ参照を使い続けないようにします
– コピー後のシートを編集する場合は、直後に`ActiveSheet`をWorksheet変数へ入れてから操作します

まとめ

Excel VBAで別のブック（ファイル）へシートをコピー・移動する方法は、基本的には`Copy`または`Move`に`Before`、`After`を指定するだけです。

でも、複数ブックを扱う処理では、アクティブブックに依存しない書き方が重要になってきます。

コピー元、コピー先、コピー後のシートをそれぞれ変数で管理すれば、コードの意図が明確になって、エラーの原因も追いやすくなります。

シートのコピー・移動は、ブック操作、ファイル操作、集計自動化の基礎になる処理です。

まずは小さなサンプルで動きを確認しながら身につけていくといいですよ。

この記事が、皆さんの実務での自動化に役立てば嬉しいです。

広告