シートのコピーを最速に理解！VBAコードで異なる結果

エクセルVBAのコード組み立てで、よく使うメソッドの１つで「Copy」メソッドがあります。

今回はこの「Copy」メソッドの使い方を説明します。

こんにちは、じゅんぱ店長（@junpa33）です。

ワークシートのコピー　VBAコードで選ぶコピー結果

エクセルでシートを「コピーする」という目的は一つではなく、例えば、

1. テンプレートがあってそのテンプレートを機能ごと複製して使いたい。
2. データ保存のためにシートをデータ共々、丸々保存したい。
3. 保存するのはデータバックアップとして数値保存を行いたい。

というようなことがあったりします。

1と2は「Worksheets.Copy」で行うことが出来ます。

3は「Cells.Copy」で行うか「Range.Value」で行うかになります。

それぞれのコピーの方法を見ていきます。

「Worksheets.Copy」メソッドでシートのコピーを行う

この様なエクセルＢＯＯＫを例にとって説明していきます。このBOOKは「.xlsmマクロ有効ブック」で作成されています。

テンプレートシートモジュールをこのように記述しておきます。

この方法は基本的に、コピー元のシートをVBAコードやセルの関数式を丸々コピー先に引き継ぎます。

Worksheets(“コピー元シート名”).Copy　でコピーする

Copyメソッドに引数を何も付けない場合です。

この場合は、新たにエクセルBOOKが作られてそこに「コピー元シート名」のシートがコピー作成されます。

このコピー方法では、新たに作成されたエクセルBOOKの保存方法を「.xlsx　Excelブック」で保存すれば、VBAコードは引き継がれません。

セルの関数式は引き継がれます。

Sub シートCopy1()
　　　Worksheets("テンプレート").Copy
End Sub

Worksheets(“コピー元シート名”).Copy After(Before)　でコピーする

Copyメソッドに引数で、「AfterやBefore」を付けてコピーする場合です。

この場合

• Afterは「コピー元シート名」の右隣にコピーされたシートが作成されます。
• Beforeは「コピー元シート名」の左隣にコピーされたシートが作成されます。

この方法は、コピー元のシートをVBAコードやセルの関数式を丸々コピー先に引き継ぎます。

Afterの場合

Sub シートCopy2()
　　　Worksheets("テンプレート").Copy After:=Worksheets("テンプレート")
End Sub

Beforeの場合

Sub シートCopy3()
　　　Worksheets("テンプレート").Copy Before:=Worksheets("テンプレート")
End Sub

Worksheets(“コピー元シート名”).Copy After(Before)　で
別BOOKにコピーする

別BOOKにコピーする場合は、事前に別BOOKを作成し開いておく必要があります。

このコピー方法は、事前に準備したエクセルBOOKの保存方法が「.xlsx　Excelブック」であれば、VBAコードは引き継がれません。

セルの関数式は引き継がれます

Sub シートCopy4()
　　　Worksheets("テンプレート").Copy _
　　　　After:=Workbooks("Book3.xlsx").Worksheets("Sheet1")
End Sub

「Cells.Copy」を使って、シートのデータだけをコピーする

データだけコピーできているかどうかを分かり易くするために、

I2セルに「=round(A1*1.1,0)」という数式を入力します。A1セルに「11」を入れます。

Worksheets(“テンプレート”).Cells.Copy Destination　でコピーする

「Destination」は、どこにコピーするかの「どこに」を示す引数になります。

今回は「Sheet2」にペーストします。

このコピー方法は、シートモジュールにあるイベントコードをコピー先に引き継ぎません。けれども、セル上に記述された関数式はコピーされます。

Sub シートCopy5()
    　Worksheets("テンプレート").Cells.Copy _
　　　　Destination:=Worksheets("Sheet2").Range("A1")
End Sub

Worksheets(“テンプレート”).Cells.Copy PasteSpecial　でコピーする

「PasteSpecial」は「形式を選択して貼り付け」メソッドです。

「Sheet3」にペーストします。

Sub シートCopy6()
    　Worksheets("テンプレート").Cells.Copy
    　Worksheets("Sheet3").Range("A1").PasteSpecial xlPasteValues
End Sub

このコピー方法は、テンプレートのコピーには不適な方法です。この例では、シートが間延びしています。関数式は引き継がれていません。

Worksheets(“テンプレート”).Cells.Copy Destination と PasteSpecial の合わせ技でコピーする

上の2つのコピー方法の合わせ技でテンプレートをコピーしながら、関数式もなしにデータだけをコピーすることが出来ます。

「Sheet4」を準備しておいてください。

Sub シートCopy7()
    　Worksheets("テンプレート").Cells.Copy _
    　Destination:=Worksheets("Sheet4").Range("A1")
    　Worksheets("Sheet4").Range("A1").PasteSpecial xlPasteValues
End Sub

この方法によって目的のコピーが達成できます。

「Range.Value」で行うシートのコピー（参照）

このコピー方法は、シートのコピーというよりはセルのコピーというよりは元セルの参照になります。

セルの値のみになります。

このコピー方法は、セルのコピーになります。この例では、セルサイズはデフォルトで値のみがコピーされます。関数式は引き継がれていません。

元セルの値が変更され、再度プロシージャが実行されたときは参照先の値も変更されます。

Sub シートCopy8()
    　Worksheets("Sheet5").Range("A1:AE19").Value = _
　　　　Worksheets("テンプレート").Range("A1:AE19").Value
End Sub

ワークシートのコピー結果　まとめ

シートのコピー方法についても、「どこへコピーするか？」だけでなく「何をコピーするか？」も考えて

コピーコードを選択しなければなりません。

「ここのコピーはいらなかったのに」と、改めてその箇所だけを削除することになるかもしれません。

新たなBOOKにペーストする場合は、関数式を引き連れて来ていれば、全てデータが変わってしまう事もありえます。

出来るだけ最初から適切なコピーコードを選択することが吉になります。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

2022年1月9日 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

Dstyle web