VBAページをアクティブにせずに異なるシートへコピペする方法を教えてください。

エクセルVBAにおいて、シート5のA1～A2をシート6のB1～B2にコピペしたいときに、
ページをアクティブにせずにやる方法を探しています。

https://www.relief.jp/docs/excel-vba-cell-copy-paste-no-sheet-change.html
を参考に以下のコードを作成しました。

VBA
1Sub コピペ()
2' １）シート5のA1～A2をシート6のB1～B2にコピペしたい
3 ThisWorkbook.Worksheets("5").Range("A1").Copy Destination:=ThisWorkbook.Worksheets("6").Range("B1")
4 ThisWorkbook.Worksheets("5").Range("A1:A2").Copy Destination:=ThisWorkbook.Worksheets("6").Range("B1:B2")
5 
6'２）下の二つはエラーになる。
7 ThisWorkbook.Worksheets("5").Range(Columns(1), Columns(2)).Copy Destination:=ThisWorkbook.Worksheets("6").Range(Columns(3), Columns(4))
8 ThisWorkbook.Worksheets("5").Range(Cells(1, 1), Cells(1, 2)).Copy Destination:=ThisWorkbook.Worksheets("6").Range(Cells(2, 1), Cells(2, 2))
9 
10 '２－１）ただし、同じシートへのコピペで、
11　　　　　シート5をアクティブな状態で実行したらエラーにはならない。
12 ThisWorkbook.Worksheets("5").Range(Columns(1), Columns(2)).Copy Destination:=ThisWorkbook.Worksheets("5").Range(Columns(3), Columns(4))
13 ThisWorkbook.Worksheets("5").Range(Cells(1, 1), Cells(1, 2)).Copy Destination:=ThisWorkbook.Worksheets("5").Range(Cells(2, 1), Cells(2, 2))
14End Sub

１）の方法でできたのですが、
以下のページにあるように、
Range("A" & i)と書くよりもCells(i,1)としたほうが、変数を用いるときに使いやすいので、
なんとか２）のようなCells(i,1)のやりかたで、アクティブにせずに他のシートにコピペする方法を
探しております。ご教授ください。よろしくお願い致します。

https://sugoikaizen.com/excelvba/column_80/

行動規範の内容に同意します

回答1件

ベストアンサー

ただ単にCellsと書くとアクティブシートが対象になってしまうので、ここで明示的にシートを指定してあげる必要があります。

VBA
1ThisWorkbook.Worksheets("5").Range(Cells(1, 1), Cells(1, 2)).Copy Destination:=ThisWorkbook.Worksheets("6").Range(ThisWorkbook.Worksheets("6").Cells(2, 1), ThisWorkbook.Worksheets("6").Cells(2, 2))

ただ、上記のように書くと長いし見づらいですよね。
そこでWithを使ったりシートをオブジェクト変数に入れたりするのが定番です。
このケースなら私はwithは使わないので、こう書きます。

VBA
1Set src = ThisWorkbook.Worksheets("5")
2Set dst = ThisWorkbook.Worksheets("6")
3src.Range(src.Cells(1, 1), src.Cells(1, 2)).Copy Destination:=dst.Range(dst.Cells(2, 1), dst.Cells(2, 2))

VBAではアクティブシートを前提にした作りは破綻しやすいので避けたほうがよいでしょう。
上記のようにシートをオブジェクト変数に入れておき、それを対象に処理することで解決できます。

投稿2018/11/08 01:03