適材適所

PowerShellを中心にプログラミングやシステム管理の備忘録的なブログ

MENU

【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

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

f:id:shinmai_papa:20210907151735p:plain

戻り値はない

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

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

f:id:shinmai_papa:20210907151730p:plain

仕方がないのでコピーされて新しく作られたシートがアクティブになる点を利用して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において頻出ですね。

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

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

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