シートのコピーを行う(Worksheet.Copyメソッド)

シートのコピーを行うにはWorksheet.Copyメソッドを使います。親オブジェクトであるWorksheetオブジェクトがコピー元のシートを示し、引数にはコピー先のシート位置を指定します。引数を省略した場合は新規ブックを作成してそちらにコピーします。

同じブック内でのコピーも可能ですし、別のブックや新規ブックへもコピー可能ですが、いずれの場合もコピー先のブックは開いている必要があります。

少しわかりにくいのが引数の「どの位置にコピーするか」という部分の書き方です。引数はBeforeとAfterの2つありますが、どちらか1つしか使えません。また、コピー先の指定方法にも、一番左にコピーする、とか、右から2番目にコピーする、などパターンがいくつかありますので、これらについての詳細は後述します。

Copyメソッドでコピーを行うと、そのコピー先でそのシートがアクティブになります。コピー直後はシート名が仮で付けられているため、必要であればNameメソッドでシート名を書き換えます。

シートのコピーにはいくつかのパターンがありますので、それぞれをコードと一緒に紹介します。



構文

Sub Worksheet.Copy([Before As Worksheet], [After As Worksheet])

引数はBeforeとAfterの2つが用意されています。BeforeとAfterの型は実際には未定義によるVariant型ですが、プログラムを書く場合はWorksheet型を指定しますので、わかりやすいようにWorksheet型としています。

BeforeとAfterの両方を省略した場合は、対象シートを新規ブックにコピーされます。

Worksheet
(親オブジェクト)
コピー元のシートとして、親オブジェクトにWorksheetオブジェクトを指定します。
Before
(省略可)
指定したシートの左にWorksheetオブジェクトのシートがコピーされます。

BeforeとAfterを同時に設定することは出来ません。設定した場合はエラーになります。

After
(省略可)
指定したシートの右にWorksheetオブジェクトのシートがコピーされます。

BeforeとAfterを同時に設定することは出来ません。設定した場合はエラーになります。



ほとんど使わない構文

なお、他にも以下のように各種オブジェクトのCopyメソッドも用意されてはいますが、これらを使うことはほとんどないと思われます。引数の考え方は上記のWorksheetオブジェクトの場合と同じです。

Sub Chart.Copy([Before As Worksheet], [After As Worksheet])
Sub DialogSheet.Copy([Before As Worksheet], [After As Worksheet])
Sub Worksheets.Copy([Before As Worksheet], [After As Worksheet])
Sub Sheets.Copy([Before As Worksheet], [After As Worksheet])

Excelでは通常使うシートといえば、「ワークシート」です。Worksheetオブジェクトとして利用します。「ワークシート」の他にも、「グラフシート」「ダイアログシート」「マクロシート」がありますが、これらはほとんど使うことはありません。

Chartオブジェクトは「グラフシート」用で、DialogSheetオブジェクトは「ダイアログシート用」です。

Worksheetsコレクションを指定する場合はワークシート(Worksheetオブジェクトのシート)を全てコピーします。グラフシート、ダイアログシート、マクロシートはコピーされません。

Sheetsコレクションを指定する場合はシートの種類に関わらず全シートをコピーします。シートの種類とは通常の「ワークシート」に「グラフシート」「ダイアログシート」「マクロシート」を合わせた4種類です。Sheetsコレクションを指定する場合は別のブックにコピーする場合が多いと思われます。その場合は親ブックとしてWorkbookオブジェクトを指定し、コピー先も同様に別ブックのWorkbook.Worksheetオブジェクトを指定することになります。



コピーしたあとのシート名は自動で設定される

シートをコピーしたあとのシート名は、コピー元のシート名がそのまま設定されます。しかし、同じ名前のシートが存在する場合は別名が付けられます。別名は「元のシート名」+「半角スペース」+「(連番)」のシート名になります。連番は2から始まります。

例えば、「Sheet1」が重複する場合は「Sheet1」と「Sheet1 (2)」になります。さらに「Sheet1」をコピーすると「Sheet1」と「Sheet1 (2)」と「Sheet1 (3)」になります。

シート名を変えたい場合はコピー後のWorksheetオブジェクトのNameプロパティで変更します。(サンプルコードで後述)



サンプルコード

シートのコピーにはいくつかのパターンがあります。それぞれコードで説明します。

なお、Copyをする際の親オブジェクトのWorksheetオブジェクトでシートを指定する方法には3つあり、シート名を指定する方法、シートの位置を指定する方法、Worksheetオブジェクトを指定する方法があります。それら詳細は「VBAでシートをコピーする際のシートの指定方法」をご参照ください。

新規ブックにコピー

Copyメソッドの引数を省略すると新規ブックにシートをコピーします。

開いている別ブックにコピー

未保存のBook7というブックにシートをコピーするサンプルです。引数BeforeによりBook7の左から2番目のシートの左にコピーします。

ブックの一番右のシートにコピー(引数Afterで指定したシートの右へコピー)

ブックの一番右にコピーするサンプルです。引数Afterを使うと、Afterで指定したシートの右側にコピーされます。

現時点での一番右のシートの位置は、Workbookオブジェクトのシート数を指すCountプロパティを使って判定できます。それを利用すると「コピー先Workbookオブジェクト.Worksheets(コピー先Workbookオブジェクト.Worksheets.Count)」と書くことで一番右のシートを指定できます。

サンプルではBook3という名前のブックをWorkbookオブジェクトとして取得し、そのブック自身の一番右に”abc”シートをコピーしています。

ブックの一番左のシートにコピー(引数Beforeで指定したシートの左へコピー)

ブックの一番左にコピーするサンプルです。引数Beforeを使うと、Beforeで指定したシートの左側にコピーされます。

現時点での一番左のシートの位置は、WorkSheets(1)で表現できます。

サンプルではBook3という名前のブックをWorkbookオブジェクトとして取得し、そのブック自身の一番左に”abc”シートをコピーしています。

シートをコピー後にシート名を変更

シートをコピー後にシート名を変更するサンプルです。

シート名の変更はコピーしたシートのWorksheetオブジェクトのNameプロパティへシート名を代入することで変更されます。

サンプルではBook3という名前のブックをWorkbookオブジェクトとして取得し、そのブック自身の一番左に”abc”シートをコピーします。

シートをコピーした直後はコピーしたシートがアクティブになるため、アクティブシートのシート名を”AAA”に変更しています。

開いていない別ブックにコピー

開いていない別ブックにコピーするサンプルです。開いていないブックにはシートのコピーはできないため、コピーを行う前に開いておきます。その後、シートのコピーを行います。

ブックのOpenメソッドを実行するとアクティブブックが開いたブックに変更されるため、事前にコピー元のアクティブブックとコピー先の開くブックのWorkbookオブジェクトを保持しています。

12行目のCopyメソッドでは、コピー元のWorkbookオブジェクトとコピー先のWorkbookオブジェクト+シートを指定しています。



ブックが開いているかどうか不明の場合

ブックが開いているかどうか不明な場合は「ブックが開かれているかチェックする」のサンプルコードをご参照ください。

ブックが開いているかどうかの分岐の条件文をそのまま使えます。

あとはIfとElseの処理以降にシートコピーの処理を書くだけです。

IsBookOpened関数とGetFileName関数は上記リンク先をご参照ください。

複数のシートを一度にコピーする場合

複数のシートを一度にコピーする場合はWorksheetsコレクションやSheetsコレクションの引数にArray関数でコピーしたいシートを全て指定して、Copyメソッドを行います。

Array関数の引数にはシート名を指定します。