シートを別のブック(の末尾)にコピー/移動する

ワークシートを別のブックにコピー/移動する方法を紹介します。

ワークシートをコピー・移動する際は、
Worksheetオブジェクトの、Copy/Moveメソッドを使用します。

ワークシートを別のブックにコピーする

Sub シートを別のブックにコピーする()

    Dim コピーするシート As Worksheet
    Set コピーするシート = ThisWorkbook.Worksheets("○○")
    
    Dim コピー先ブック As Workbook
    Set コピー先ブック = Workbooks("△△.xlsx")
    
    ' 指定ブックの先頭にコピー
    コピーするシート.Copy Before:=コピー先ブック.Worksheets(1)
    
    ' 指定ブックの第2シートと第3シートの間に挿入
    コピーするシート.Copy After:=コピー先ブック.Worksheets(2)
    コピーするシート.Copy Before:=コピー先ブック.Worksheets(3)
        ' ↑どちらでもOK
    
    ' 指定ブックの末尾にコピー
    コピーするシート.Copy After:=コピー先ブック.Worksheets(コピー先ブック.Worksheets.Count)
        
End Sub

見ての通りという感じですが、Copyメソッドの引数「After/Before」には別のブックのシートも特に制約なく指定できます。

あとは、コピー先ブックのどの位置に挿入するかを、
シート番号やシート名で指定してあげればOKです。

ワークシートを別のブックに移動する

CopyをMoveに変えるだけで、他は全く同じコードで動きます。

Sub シートを別のブックに移動する()

    Dim 移動するシート As Worksheet
    Set 移動するシート = ThisWorkbook.Worksheets("○○")
    
    Dim 移動先ブック As Workbook
    Set 移動先ブック = Workbooks("△△.xlsx")
    
    ' 指定ブックの先頭に移動
    移動するシート.Move Before:=移動先ブック.Worksheets(1)
    
    ' 指定ブックの第2シートと第3シートの間に挿入
    移動するシート.Move After:=移動先ブック.Worksheets(2)
    移動するシート.Move Before:=移動先ブック.Worksheets(3)
        ' ↑どちらでもOK
    
    ' 指定ブックの末尾に移動
    移動するシート.Move After:=移動先ブック.Worksheets(移動先ブック.Worksheets.Count)
        
End Sub

一応補足ですが、コピーと違い↑のコードをこのまま実行すると、
「オートメーションエラー」になります。

最初の「指定ブックの先頭に移動」した時点で、
移動元のシートが元のブックからはなくなってしまっていますからね。

サンプルコードとして持って行く場合は、これらの内の1つのコードしか使わないでしょうから別に問題ないです。

「オートメーションエラー」はエラーが出るパターンが多く、
また説明になっていないエラーメッセージのため、出ると結構焦るエラーです。

とりあえず今回は、
「参照していたワークシートやワークブックが消えた場合にも出る」
ということを、心の片隅にでも置いておいてください。

コピー/移動後にそのシートを編集する場合

コピー/移動後にそのシートを編集する場合は、Copy/Moveメソッドの特徴である
「完了後はそのシートがアクティブになっている」ことを利用します。

コピーするシート.Copy Before:=コピー先ブック.Worksheets(1)

Dim 作成シート As Worksheet
Set 作成シート = ActiveSheet

作成シート.Name = "□□"
作成シート.Range("A1") = 1

このように、コピー直後にActiveSheetを変数にSetすることで、
コピーしたシートを編集するコードを書くことができます。

なお、完了後にそのシートがアクティブになっているからと言って、

コピーするシート.Copy Before:=コピー先ブック.Worksheets(1)

ActiveSheet.Name = "□□"
Range("A1") = 1

こんな風にアクティブシートに直接処理をするのは、なるべくやめましょう。

「シートを別のブックにコピーする」処理を書くようなマクロでは、
いつActiveSheetが変わってもおかしくないですからね。

VBAを覚えたての頃は、こういった記述もわかりやすくてよいのですが、
ブック・シート間のやりとりが複雑になると、
ActiveSheetを頼ったコーディングはバグの元になります。

処理するシートをしっかり変数に入れて、RangeやCellsの親シートを省略しないマクロを書くようにしましょう。

ちなみに、この変数にSetして処理を行うという観点で、
Copy/Moveメソッドが、かゆいところに手が届かないやつだなと思うのが、
メソッドの返り値で移動後のWorksheetをくれないことです。

どういう意味かと言いますと、例えばコピー完了後にそのシートを編集したいときに、

Dim 作成シート As Worksheet
Set 作成シート = コピーするシート.Copy(Before:=コピー先ブック.Worksheets(1)) ' エラー

作成シート.Name = "□□"
作成シート.Range("A1") = 1

こんな風に「Copyの結果作られたシートを変数にSetして、そのまま編集コードを書く」ことができればよいのですが、残念ながらこれができません。

新規シートの挿入である「Addメソッド」さんならできるんですけどね。

Dim 作成シート As Worksheet
Set 作成シート = ThisWorkbook.Worksheets.Add ' ←これはOK

　
この仕様が何かとめんどくさいのですが、どうしようもないので、ActiveSheetを変数にSetして頑張りましょう。

末尾のシートの取得方法について

別のブックへシートをコピーというと、末尾に入れることが多いでしょう。
その時はこんなコードを書いていました。

コピーするシート.Copy After:=コピー先ブック.Worksheets(コピー先ブック.Worksheets.Count)

一応解説しますと、コピー先ブックのワークシートの中で、
ワークシートの数番目のシート＝末尾シートと言うことになります。

理屈は簡単なのですが、これが長ったらしくて面倒なんですよね。

コピー先のブックをWorkbooks("△△△△.xlsx")で取得していると、

コピーするシート.Copy After:=Workbooks("△△△△.xlsx").Worksheets(Workbooks("△△△△.xlsx").Worksheets.Count)

みたいになって腹が立つのですが、
でもこのために変数にするのも面倒だったりします。

このように、同じようなコードが呪文みたいに続く冗長なイディオムは、
Functionプロシージャを使って汎用関数にしておくととてもスッキリします。

Function Get最終シート(指定ブック As Workbook) As Worksheet
    Set Get最終シート = 指定ブック.Worksheets(指定ブック.Worksheets.Count)
End Function

コピーするシート.Copy After:=Workbooks("△△△△.xlsx").Worksheets(Workbooks("△△△△.xlsx").Worksheets.Count)
' ↓汎用関数を使って書き換え
コピーするシート.Copy After:=Get最終シート(Workbooks("△△△△.xlsx"))

関数の中身は超単純で、さっきのイディオムを書いているだけですが、
実際のコードが素晴らしく読みやすくなっているのが分かると思います。

自作関数というと難しく感じるかもしれませんが、
こういった単純な関数ならすごく簡単に作れます。

関数づくりの練習にもなりますので、こういった冗長なコードを見つけたら、関数化にチャレンジしてみましょう。