【Excel VBA】別ファイルのシートをマクロを実行しているファイルにシートごとコピーする

+IT 清水です。

「別のExcelファイルからマクロを実行しているファイルにシートをコピーしたい」という質問をいただきました!

「別ファイルのシートをコピーする」ような記事はいくつかあるのですが、
別ファイルにコピーする内容だったり、新しいファイルにコピーするものが多かったので、
今回は、より実践的な内容として、マクロを実行しているExcelファイルを指定して別Excelファイルからシートをコピーする方法をご紹介します。

  通常、Workbooks().openを実行すると、開かれたExcelファイルがアクティブになります。  
その後、Worksheets().Copyを実行すると、アクティブになっているExcelファイルにコピーされるので、
開かれた方のExcelファイルにコピーされ、マクロを実行したExcelファイルにシートがコピーされません。

ポイントは、
 マクロを実行しているExcel Workbookオブジェクトをあらかじめ変数に格納しておくことで、
どのExcelファイルが現在アクティブになっているかを意識することなく、マクロを実行できるところです。  

マクロを実行しているExcelファイルに別ファイルのシートをコピーする

今回の例では、「マクロ実行元」というExcelに「概要」「設定」シートがあるものとし、「概要」シートの後ろに新しく「インポート先」Excelの「データ」シートをコピーするものとします。

'マクロ実行元
Dim macroWb As Workbook

'インポート先
Dim importWb As Workbook
Dim importPath As String

'マクロを実行元のWorkbook
Set macroWb = ThisWorkbook

'インポート先の文字列を取得
importPath = "C:\Users\example\インポート先のExcel.xlsx"

'データをインポート(シートの内容をコピー)
Set importWb = Workbooks.Open(importPath)
importWb.Worksheets("データ").Copy After:=macroWb.Worksheets("概要") 'Afterの場合、指定したシートの後ろに
importWb.Close

実行すると以下のようになります。

プログラムはほとんど同じですが、Workbookオブジェクトの代わりに、Worksheetオブジェクトを指定することもできます
また、CopyのオプションをAfterからBeforeに変更すると、対象のシートの前にコピーすることができます。

詳しくは、Copyのリファレンスを参照してください。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheets.copy

Worksheetをオブジェクトに設定する場合

'マクロ実行元(勤務表シート)
Dim workTimeSt As Worksheet

'インポート先
Dim importWb As Workbook
Dim importPath As String

'マクロを実行元のWorkbook
Set workTimeSt = ThisWorkbook.Worksheets("勤務表")

'インポート先の文字列を取得
importPath = "C:\Users\example\インポート先のExcel.xlsx"

'データをインポート(シートの内容をコピー)
Set importWb = Workbooks.Open(importPath)
importWb.Worksheets("データ").Copy After:=workTimeSt 'Beforeの場合、指定したシートの前に
importWb.Close

実行すると以下のようになります。

WorkbookオブジェクトやWorksheetsオブジェクトを変数に格納しておく方法は、シートをまたいだ処理をするマクロの場合にはかなり有効ですので、ぜひ活用してみてください!

まとめ

今回は質問のあったことを記事にさせていただきました!
これからもこういった質問を随時受け付けておりますので、お気軽にお問い合わせください。

また、+ITでは、わいわい会という自習会を定期的に開催しています。
最近のことについて雑談される方、 キャリアの相談や技術的な質問をされる方といった、いろんな方にご参加いただいています!

講座を受講される方も、自分で勉強する方も時間を共有するので、一緒に学ぶ仲間も見つかると思います。
興味のある方は、ぜひご参加ください!