VBAでシートのコピーを行う（Worksheet.Copy）

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

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

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

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

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

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

構文

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

引数はBeforeとAfterの２つが用意されています。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コレクションを指定する場合はシートの種類に関わらず全シートをコピーします。シートの種類とは通常の「ワークシート」に「グラフシート」「ダイアログシート」「マクロシート」を合わせた４種類です。Sheetsコレクションを指定する場合は別のブックにコピーする場合が多いと思われます。その場合は親ブックとしてWorkbookオブジェクトを指定し、コピー先も同様に別ブックのWorkbook.Worksheetオブジェクトを指定することになります。

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

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

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

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

サンプルコード

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

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

新規ブックにコピー

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

Sub SheetCopyNewBookTest()
    Call Sheets(1).Copy
End Sub

Sub SheetCopyNewBookTest()

Call Sheets(1).Copy

End Sub

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

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

Sub SheetCopyTest1()
    Call Sheets(1).Copy(before:=Workbooks("Book7").Worksheets(2))
End Sub

Sub SheetCopyTest1()

Call Sheets(1).Copy(before:=Workbooks("Book7").Worksheets(2))

End Sub

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

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

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

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

Sub SheetCopyRightTest()
    Dim wb  As Workbook
    
    Set wb = Workbooks("Book3")
    
    Call wb.Worksheets("abc").Copy(After:=wb.Worksheets(wb.Worksheets.Count))
End Sub

Sub SheetCopyRightTest()

Dim wb As Workbook

Set wb = Workbooks("Book3")

Call wb.Worksheets("abc").Copy(After:=wb.Worksheets(wb.Worksheets.Count))

End Sub

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

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

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

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

Sub SheetCopyLeftTest()
    Dim wb  As Workbook
    
    Set wb = Workbooks("Book3")
    
    Call wb.Worksheets("abc").Copy(Before:=wb.Worksheets(1))
End Sub

Sub SheetCopyLeftTest()

Dim wb As Workbook

Set wb = Workbooks("Book3")

Call wb.Worksheets("abc").Copy(Before:=wb.Worksheets(1))

End Sub

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

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

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

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

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

Sub ChangeSheetNameTest()
    Dim wb  As Workbook
    
    Set wb = Workbooks("Book3")
    
    Call wb.Worksheets("abc").Copy(Before:=wb.Worksheets(1))
    
    ActiveSheet.Name = "AAA"
End Sub

Sub ChangeSheetNameTest()

Dim wb As Workbook

Set wb = Workbooks("Book3")

Call wb.Worksheets("abc").Copy(Before:=wb.Worksheets(1))

ActiveSheet.Name = "AAA"

End Sub

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

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

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

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

Sub SheetCopyTest2()
    Dim wbActive    As Workbook '// アクティブブック用
    Dim wbOpen      As Workbook '// コピー先ブック用
    
    '// アクティブブックを保持
    Set wbActive = ActiveWorkbook
    
    '// ブックを開く
    Set wbOpen = Workbooks.Open("C:\web\Book4.xlsx")
    
    '// 開いたブックの一番左のシートの右にコピー
    Call wbActive.Sheets(1).Copy(After:=wbOpen.Worksheets(1))
End Sub

Sub SheetCopyTest2()

Dim wbActive As Workbook '// アクティブブック用

Dim wbOpen As Workbook '// コピー先ブック用

'// アクティブブックを保持

Set wbActive = ActiveWorkbook

'// ブックを開く

Set wbOpen = Workbooks.Open("C:\web\Book4.xlsx")

'// 開いたブックの一番左のシートの右にコピー

Call wbActive.Sheets(1).Copy(After:=wbOpen.Worksheets(1))

End Sub

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

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

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

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

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

Sub SheetCopyOtherTest()
    Dim bOpened     As Boolean  '// コピー先ブックの状態判定フラグ
    Dim wbActive    As Workbook '// アクティブブック用
    Dim wbOpen      As Workbook '// コピー先ブック用
    Dim sFilePath               '// ファイルパス
    Dim sFileName               '// ファイル名
    
    '// アクティブブックを保持
    Set wbActive = ActiveWorkbook
    
    '// 指定ファイルパスが開いているか確認
    sFilePath = "C:\web\Book4.xlsx"
    bOpened = IsBookOpened(sFilePath)
    
    '// ブックが開いている場合
    If (bOpened = True) Then
        '// 開いているブックのWorkbookオブジェクトを取得
        Call GetFileName(sFilePath, sFileName)
        Set wbOpen = Workbooks(sFileName)
    '// ブックが開いていない場合
    Else
        '// 開いていないブックを開きWorkbookオブジェクトを取得
        Set wbOpen = Workbooks.Open(sFilePath)
    End If
    
    '// シートコピー
    Call wbActive.Sheets(1).Copy(After:=wbOpen.Sheets(1))
End Sub

Sub SheetCopyOtherTest()

Dim bOpened As Boolean '// コピー先ブックの状態判定フラグ

Dim wbActive As Workbook '// アクティブブック用

Dim wbOpen As Workbook '// コピー先ブック用

Dim sFilePath '// ファイルパス

Dim sFileName '// ファイル名

'// アクティブブックを保持

Set wbActive = ActiveWorkbook

'// 指定ファイルパスが開いているか確認

sFilePath = "C:\web\Book4.xlsx"

bOpened = IsBookOpened(sFilePath)

'// ブックが開いている場合

If (bOpened = True) Then

'// 開いているブックのWorkbookオブジェクトを取得

Call GetFileName(sFilePath, sFileName)

Set wbOpen = Workbooks(sFileName)

'// ブックが開いていない場合

Else

'// 開いていないブックを開きWorkbookオブジェクトを取得

Set wbOpen = Workbooks.Open(sFilePath)

End If

'// シートコピー

Call wbActive.Sheets(1).Copy(After:=wbOpen.Sheets(1))

End Sub

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

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

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

Sub SheetsCopyTest()
    '// Nameプロパティを利用
    Call Sheets(Array(Sheets(1).Name, Sheets(2).Name)).Copy
    
    '// シート名を利用
    Call Sheets(Array("Sheet1", "Sheet2")).Copy
End Sub

Sub SheetsCopyTest()

'// Nameプロパティを利用

Call Sheets(Array(Sheets(1).Name, Sheets(2).Name)).Copy

'// シート名を利用

Call Sheets(Array("Sheet1", "Sheet2")).Copy

End Sub

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

構文

ほとんど使わない構文

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

サンプルコード

新規ブックにコピー

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

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

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

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

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

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

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

関連記事