【Excel VBA】シートをコピーする方法の解説と要望

たまにVBA触ると忘れているので備忘録に残しておきます。

Excel VBAで頻出のシートコピーについてです。

WorksheetのCopyメソッド

WorksheetのCopyメソッドを使用することでシートをコピーできます。

Copyメソッドにはafterとbeforeでコピー位置を指定することができます。

コピー位置はシート名ではなく、どちらもworksheetオブジェクトを指定します。

個人的にはここにいつもハマります。いつもシート名を指定してしまいます。

どちらも指定しないと、新しいブックにコピーされます。

 

Sub Sheet1Copy()
   'シートのindexを指定する
   Worksheets(1).Copy
End Sub

Sub SheetCopy()
   '何も指定しないと新しいブックにコピーされる
   Worksheets("コピーしたいシート名").Copy
End Sub

Sub SheetCopyBefore()
   'beforeに指定したシートの前にコピーされる
   Worksheets("コピーしたいシート名").Copy before:=Worksheets("コピー先の前のシート名")
End Sub

Sub SheetCopyAfter()
   'afterに指定したシートの後ろにコピーされる
   Worksheets("コピーしたいシート名").Copy before:=Worksheets("コピー先の後ろのシート名")
End Sub

Sub SheetCopyTop()
   '先頭にコピーする
   Worksheets("コピーしたいシート名").Copy before:=Worksheets(1)
End Sub

Sub SheetCopyEnd()
   '末尾にコピーする
   Worksheets("コピーしたいシート名").Copy before:=Worksheets(Worksheets(Worksheets.Count))
End Sub

afterとbeforeを両方指定するとエラーになります。

 

Sub SheetCopyErr()
   'afterとbefore指定するとエラーになる
   Worksheets("Sheet1").Copy before:=Worksheets("Sheet1"), after:=Worksheets("Sheet3")
End Sub

ちなみにこんなエラーがでてきます。これじゃ何が悪いのかわからん・・・。

戻り値はない

オブジェクトライブラリを見るとSubと定義されており、戻り値はありません。

できればコピーして新しくできたWorksheetオブジェクトでも返してくれればよかったのですが・・・。

仕方がないのでコピーされて新しく作られたシートがアクティブになる点を利用してActiveSheetで取得するしかありません。

新しいシート名を変更するときは、本当はこんな風にやりたい。

'エラーになるよ
Dim sh as Worksheet
set sh = Worksheets(1).copy
sh.name="新しいシート"

でも、Copyメソッドに戻り値がないため、こうするしかない。

'動くよ
Dim sh as Worksheet
Worksheets(1).copy
set sh = ActiveSheet
sh.name="新しいシート"

うーん、残念。

終わりに

シートのコピーはExcel VBAにおいて頻出ですね。

これができると一気にできることが広がります。

戻り値がない等、個人的には使い勝手がイマイチな点もありますが、絶対に使い方は一通り抑えておきたいところです。

というわけで、ここまでお読みいただき、ありがとうございました。