エクセルVBAでシートのコピーを自動化！削除・追加も解説

紬ちゃん

エクセルVBAでシートのコピーってできるの？難しい？

ライクさん

シートをコピーするマクロって、実はとても簡単だよ！

今回はエクセルVBAを使って、シートのコピーを自動化する方法について解説します。

併せて、シートの削除などシート操作についてもご紹介します。

シートをコピーするVBAのコードは、基本的に1行で書けてとても簡単です。

コピペで使えるサンプルコードもぜひご活用ください。

また、シートのコピーのほか、エクセルマクロの基礎が学べる記事を次の通りまとめました。

マクロの有効化やボタンの設置など、マクロ初心者が初めに知っておきたい内容をチェックできて便利です。

エクセルVBAでシートをコピーする基本の方法

エクセルVBAなら、同じ内容のシートを自動で何枚もコピーできます。

まずは、いちばんシンプルなシートコピーのコードを見てみましょう。

Sub シートをコピーする()
    Worksheets("Sheet1").Copy
End Sub

このマクロを実行すると、「Sheet1」という名前のシートをコピーします。

重要ポイントは、「コピーしたいシートの名前」を正しく書くことです。

シート名は全角・半角・スペースも含めて完全に一致させる必要があります。

以下のように、エクセルの画面下にあるシート名をコピーするのが最も確実です。

この基本形を覚えておけば、日報テンプレートの複製や、月ごとのシート作成などの繰り返し作業を自動化できるようになります。

シートをコピーする具体的な使い方について、もう少し詳しく見ていきましょう。

シートのコピーの基本操作をマスターしよう

「テンプレートのシートを毎日分コピーしたい」「月初に新しいシートを作りたい」など、シートをコピーする場面はとても多いです。

以下の「1週目」シートをコピーして、シート名を変える手順を解説します。

シートをコピーしたいエクセルファイルを開き、以下の手順でVBAコードを書いていきましょう。

STEP

Visual Basicを選択する

• 「開発」タブをクリック
• 「Visual Basic」を押す

STEP

標準モジュールを挿入

• 「挿入」タブを押す
• 「標準モジュール」をクリック

STEP

プロシージャを選択

• 「挿入」タブをクリック
• 「プロシージャ」を選ぶ

STEP

マクロ名を登録

• マクロ名を入力
• 「OK」ボタンを押す

STEP

VBAコードをコピー

以下のコードをコピーします。

Public Sub シートをコピー()

'以下をコピー

    Worksheets("1週目").Copy
    ActiveSheet.Name = "2週目"

'コピーここまで

End Sub

ActiveSheet.Name="変更するシート名"でシート名を変更します。

コピー直後のシートが自動的に選ばれるため、変更元のシートを選択しなくても名前を変更できます。

STEP

マクロを実行する

• 「実行」ボタンをクリック
• マクロ名を選択
• 「実行」ボタンを押す

STEP

シートのコピーができた

新しいエクセルファイルにシートのコピーができたことを確認しましょう。

コピー先の位置を指定する方法

先ほどのVBAでは、新しいエクセルファイルにシートがコピーされました。

コピー先を指定しないままシートをコピーすると、エクセルは自動的に新しいファイルを作ります。

そのため、「同じファイルにコピーしたつもりなのに別のファイルが開いた」という現象が起こります。

先ほどの例で、「1週目」シートと同じファイルにコピーしたい場合は、次のようにコードを書きましょう。

Public Sub 位置を指定してコピー()

    ThisWorkbook.Worksheets("1週目").Copy _
        After:=ThisWorkbook.Worksheets("1週目")

    ActiveSheet.Name = "2週目"
    
End Sub

ThisWorkbook.Worksheets("1週目").Copy After:=ThisWorkbook.Worksheets("1週目")はもともとひとつのコードですが、After:=以降のコードを見やすくするために、コードの途中で改行しています。

ThisWorkbook.Worksheets("1週目").Copyの後ろに改行するためのコード_を入れています。

このマクロを実行すると、「1週目」シートの右側にシートがコピーされます。

ライクさん

1週目シートの左側にコピーしたい場合は、AfterをBeforeに変更するだけでOKだよ！

別ファイルにエクセルシートをコピーするマクロ

エクセルシートは別のファイルへもコピーできます。

「集計結果だけ別ファイルに保存したい」「提出用のファイルを作りたい」場合に活用しましょう。

まずは、基本のコードを見てみましょう。

Sub 別ファイルへコピーする()
    Worksheets("1週目").Copy _
    After:=Workbooks("出力.xlsx").Worksheets(1)
End Sub

このマクロを実行すると、次の操作が行われます。

• 「1週目」という名前のシートをコピー
• 「出力.xlsx」というエクセルファイルの前から2番目にシートがコピーされる

Worksheets(1)とは「左から数えて1番目のシート」という意味です。

1番目のシートの後にシートをコピーするため、結果としてシートの2番目に「1週目」シートがコピーされます。

VBAで別ファイルへコピーする基本パターン

実務では、コピー先のファイルをファイル選択ダイアログで選んで、保存まで一気に行うことが多いです。

サンプルコードは次のとおりです。

Sub 別ファイルを選択してコピーする()

    Dim wb As Workbook
    Dim fpath As Variant

    ' ファイル選択ダイアログを表示
    fpath = Application.GetOpenFilename( _
        FileFilter:="Excelファイル (*.xlsx;*.xlsm), *.xlsx;*.xlsm")

    ' キャンセルされた場合は処理を終了
    If fpath = False Then Exit Sub

    ' 選択したファイルを開く
    Set wb = Workbooks.Open(fpath)

    ' シートをコピー
    ThisWorkbook.Worksheets("1週目").Copy _
        After:=wb.Worksheets(1)

    ' 保存して閉じる
    wb.Save
    wb.Close

    MsgBox "シートのコピーが完了しました。"

End Sub

このマクロを実行すると、次の操作が行われます。

• ファイル選択ダイアログを表示し、コピー先のファイルを選ぶ
• 「1週目」シートをコピーする
• コピー先のファイルのシートの2番目にコピー
• ファイルを上書き保存
• ファイルを閉じる
• 作業終了をお知らせするメッセージボックスを表示

エクセルVBAでファイルを開く操作を自動化することで、手動でファイルを開かずに別ファイルからシートをコピーできて便利です。

マクロを含めてコピーする際の注意点

マクロボタンが含まれるシートを別のファイルにコピーしたあと、マクロが実行できる場合とできない場合があります。

これは、マクロがどこに書かれているかによって、動き方が変わるためです。

ここでは、よくある2つのケースに分けて説明します。

シートにマクロが登録されている場合

以下のように、標準モジュールではなくシートにマクロが登録されている場合があります。

ライクさん

シートに属したモジュールのことを「シートモジュール」と言うよ！

この場合は、シートをコピーすると、マクロも一緒にコピーされます。

コピーしたシートでもボタンはそのまま動くことが多いです。

標準モジュールにマクロが登録されている場合

マクロ初心者の場合、標準モジュールにマクロを記述することがほとんどです。

ライクさん

PC-Likeの記事でも、標準モジュールに記述しているよ！

標準モジュールにマクロを記述している場合、シートだけコピーしても、マクロまでは一緒にコピーされません。

そのため、「ボタンを押しても何も起きない」「以前は動いていたのに動かなくなる」といった現象が起こることがあります。

この場合、マクロ自体もコピー先のファイルに用意する必要があります。

なお、「ThisWorkbook」に登録されるブックモジュールの場合も、シートコピーではマクロがコピーされませんのでご注意ください。

別ファイルへシートをコピーして、そのシートでもマクロを動かしたい場合は、次のように対応しましょう。

• コピー先のファイルを「xlsm」で作成
• 標準モジュールを作成し、必要なマクロをあらかじめコピーしておく
• シートをコピー

マクロ自体をコピーするマクロもありますが、初心者の場合は上記の手順のほうが簡単で確実なのでお勧めです。

エクセルシートをコピーするVBAと一緒に覚えておきたい便利技

エクセルのシートをコピーするVBAと合わせて知っておきたいコードについて、以下のとおりまとめました。

• シートを削除・初期化
• シートを追加
• シートを指定
• シート名を取得・変更

これらのシート操作を知っておくことで、自動化の幅がグンと広がります。

いずれも簡単にできますので、ぜひ試してみてください。

VBAでシートを削除・初期化する方法

シートを削除したり初期化するVBAをご紹介します。

検算用にコピーして使ったシートを削除したり、前年度のシートを初期化して使いまわす際に知っておくと便利です。

作例では、上記の「2週目」シートを削除・初期化するエクセルVBAをご紹介します。

以下の3種類について解説しました。

• シートを丸ごと削除するVBA
• シートは残して中身だけ削除する場合
• シートと書式は残して文字を削除するVBA

いずれのコードも、シート名を変更するだけでそのまま使えます。

状況に応じて使い分けましょう。

シートを丸ごと削除するVBA

エクセルのシートごと削除したい場合は次のVBAを使いましょう。

シート名を削除したいシートに変更してください。

Sub シートを丸ごと削除()

    Application.DisplayAlerts = False

    Worksheets("2週目").Delete

    Application.DisplayAlerts = True

End Sub

なお、1行目のApplication.DisplayAlerts = Falseでシートを削除する際の確認メッセージを一時的に非表示にし、3行目のApplication.DisplayAlerts = Trueで非表示を解除させています。

上記のようなメッセージを毎回表示させたい場合は、コードの1行目と3行目を削除してください。

シートは残して中身だけ削除する場合

シート自体は残して、記載されている内容をすべて削除したい場合は次のVBAを使いましょう。

Sub シートの中身を消す()

    Worksheets("2週目").Cells.Clear

End Sub

Worksheets("2週目").Cellsで「2週目」シートのすべてのセルを選択し、.Clearでセルの内容が削除されます。

このマクロを実行すると、以下のようにシートの中身のみ削除されます。

行・列の大きさは初期化されないことに注意が必要です。

シートと書式は残して文字を削除するVBA

毎月使っている表のデータだけリセットしたい、でも数式は残しておきたい場合は、次のVBAを使いましょう。

Sub シートの手入力を消す()

Worksheets("2週目").UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

End Sub

このマクロを実行すると、以下のようになります。

• 手入力した文字や数字：消える
• 数式：残る
• セルの色や枠線などの書式：残る

SpecialCells(xlCellTypeConstants)で、手入力されたセルのみ選択するよう指示しています。

なお、この場合は手入力したタイトルや項目まで削除されてしまいます。

削除されたくない文字は、「=””」で囲んでおきましょう。

フォーマットを作成する際に、タイトルなどをあらかじめ上記のように入力しておくことで、効率よく手入力した文字・数字のみ削除することができるのでお勧めします。

エクセルVBAでシート追加を自動化しよう

エクセルVBAで新しいシートの追加も自動化できます。

集計用のシートを別途設ける場合や、提出用の表紙を自動作成する場合に便利です。

Sub 新しいシートを追加する()

Worksheets.Add.Name = "新しいシート"

End Sub

このマクロを実行すると、「新しいシート」という名前のシートがファイルに追加されます。

シート名を変更して活用しましょう。

また、シートの追加する場所を指定したい場合は、以下のように記述します。

Sub シート追加の位置を指定する()

    Worksheets.Add Before:=Worksheets("1週目")
    
    ActiveSheet.Name = "新しいシート"
    
End Sub

このマクロを実行すると、「1週目」シートの左側に「新しいシート」という名前のシートが追加されます。

1行目のBeforeをAfterに変更すると、「1週目」シートの右側に追加されますので、状況に応じて使い分けましょう。

ライクさん

シートを「左から●番目」に追加する書き方は、次の章で解説するよ！

エクセルシートを指定するマクロを解説

コピーするエクセルシートを指定する際など、状況に応じてシートの指定方法を変えると効率が良いです。

エクセルのマクロでシートを指定する方法は、次の3パターンがあります。

シート名で指定

Worksheets("シート名")

マクロに慣れるまでは、名前指定が一番分かりやすいのでお勧めです。

左から何番目で指定

ファイルが1月から12月まで並んでいる場合など、「左から何番目」と指定することもできます。

Worksheets(4)

この場合は「左から4番目」のシートを指定しています。

変数に入れて使う

シート名が長くてVBAに記述すると見づらくなる場合などは、変数に入れて使うこともできます。

Dim ws As Worksheet
Set ws = Worksheets("シート名")

この場合、変数wsの種類がワークシートであることを指定し、「シート名」を変数に格納しています。

前章でシートの追加する場所を指定する方法を解説しましたが、「左から●番目」に追加する際のお勧めの書き方があります。

なお、シートをコピーするときも同じ考え方をします。

Sub 左から4番目にシート追加()

    Worksheets.Add Before:=Worksheets(4)

    ActiveSheet.Name = "新しいシート"

End Sub

このマクロを実行すると、次のような動きをします。

• 左から4番目のシートの手前にシートが追加される
• もともと4番目だったシートは右にずれて5番目になる
• 追加されたシートに「新しいシート」というシート名が設定される

Before:=Worksheets(4)の代わりにAfter:=Worksheets(3)でも左から4番目にシートを追加することができます。

しかし、後からVBAを読み返したり、担当者が変わったりした場合にBefore:=Worksheets(4)のほうが「何番目に入れたいか」が分かりやすいのでお勧めです。

シート名を取得・変更するエクセルVBA

エクセルで作業をしていると、シート名を自動で変更したい場面がよくあります。

VBAを使えば、今、選択しているシートの名前を調べたり、シート名を変更したりできます。

それでは基本のコードを見てみましょう。

Sub シート名を取得()

    MsgBox ActiveSheet.Name
    
End Sub

このコードを実行すると、メッセージボックスに、今、クリックしているシート名が表示されます。

シート名の変更は以下のコードを使います。

Sub シート名を変更()

    ActiveSheet.Name = "6週目"
    
End Sub

上の2つのコードを組み合わせれば、「シートの存在チェックをして、条件に当てはまればシート名を変更するVBA」を作れます。

以下がサンプルコードです。コピーして活用する場合は、シート名を変更してください。

Sub 条件付きでシート名を変更する()

    Dim sheetName As String
    
    sheetName = ActiveSheet.Name

    If sheetName = "5週目" Then
        ActiveSheet.Name = "6週目"
        MsgBox "シート名を「6週目」に変更しました"
        
    Else
        MsgBox "シート名は変更されませんでした"
    
    End If

End Sub

コードの意味は以下のとおりです。

Dim sheetName As String

変数sheetNameの種類がStringであることを宣言しています。

ライクさん

Stringとは文字列のことだよ！

sheetName = ActiveSheet.Name

今、クリックしているシートの名前を変数sheetNameに格納しています。

If～Then～Else～End If

If文に当てはまる場合はThen以下の処理を行い、当てはまらない場合はElse以下の処理を行います。

今回の場合は次の処理が行われています。

• 条件：変数sheetNameに格納されたシート名が「5週目」である
• 当てはまる：シート名を「6週目」に変更し、メッセージボックスを表示させる
• 当てはまらない：シート名が変更されなかった旨のメッセージボックスを表示

実務では、「下書き」シートを「確定」に変更したり、年度の更新をする場合に活用できます。

効率化やミス防止に便利ですので、シートのコピーとセットで覚えておきましょう。

エクセルVBAのシートコピーに関するQ＆A

VBAで別ファイルを開かずにシートをコピーできますか？

VBAではコピー先のファイルは必ず開く必要があります。

エクセルでは閉じたままのファイルの中身を直接操作することができないからです。

ただ、ファイルを開くことを自動化することはできます。

詳しくは記事内の「VBAで別ファイルへコピーする基本パターン」をご覧ください。

VBAでシート全体をコピーして別シートに貼り付けることはできますか？

シートそのものをコピーするほうが簡単なのでお勧めです。

記事内の「シートのコピーの基本操作をマスターしよう」をご確認ください。

中身だけを既存のシートに貼り付けたい場合は、次のようにシート内のセル全体をコピーし、既存のシートのA1セルに貼り付けましょう。

Sub シート内容を既存のシートに貼り付ける()

    Worksheets("コピー元のシート名").Cells.Copy
    Worksheets("既存のシート名").Range("A1").PasteSpecial xlPasteAll

    Application.CutCopyMode = False

End Sub

VBAでシートをコピーした場合、書式はそのままですか？

シートを丸ごとコピーした場合、レイアウトや書式、数式などがそのままコピーされます。

マクロで自動的にシートをコピーしよう！

この記事ではエクセルVBAを使ってシートをコピーする方法について解説しました。

基本的にコードが1行のみですので、手作業でコピーするよりも簡単・確実にシートをコピーできます。

最後に、コードのおさらいです。

シートのコピーや削除などシート管理が一気に楽になるコードを押さえておきましょう。

エクセルマクロについて、業務の効率化や作業ミス防止に役立つ内容を記事にまとめました。

記事をチェックして、繰り返し作業からの卒業を目指しましょう！