エクセル 何故かシート間の値のコピーが出来ない

質問

いつもお世話になります。

開いているブックのシート「リスト1～3」に、Book1.xlsの「リスト1～3」の値をコピーする為に、下記のマクロを作成しました。

Dim SH1, SH2, SH3, SH4, SH5, SH6 As Worksheet
Set SH1 = ThisWorkbook.Worksheets("リスト1")
Set SH2 = ThisWorkbook.Worksheets("リスト2")
Set SH3 = ThisWorkbook.Worksheets("リスト3")
Set SH4 = Workbooks("Book1.xls").Worksheets("リスト1")
Set SH5 = Workbooks("Book1.xls").Worksheets("リスト2")
Set SH6 = Workbooks("Book1.xls").Worksheets("リスト3")

'リスト1をコピーする

D = SH4.Range("A1").CurrentRegion.Rows.Count
E = SH4.Range("A1").CurrentRegion.Columns.Count
SH1.Range(Cells(1, 1), Cells(D, E)).Value = SH4.Range("A1").CurrentRegion.Value

'リスト2をコピーする

F = SH5.Range("A1").CurrentRegion.Rows.Count
G = SH5.Range("A1").CurrentRegion.Columns.Count

SH2.Range(Cells(1, 1), Cells(F, G)).Value = SH5.Range("A1").CurrentRegion.Value

'リスト3をコピーする
H = SH6.Range("A1").CurrentRegion.Rows.Count
I = SH6.Range("A1").CurrentRegion.Columns.Count
SH3.Range(Cells(1, 1), Cells(H, I)).Value = SH6.Range("A1").CurrentRegion.Value

以上を実行すると、「アプリケーション定義またはオブジェクト定義のエラーです」とエラーメッセージが出てしまいます。
それぞれのシートの処理の時に、
SH1.Select
SH2.Select
SH3.Select
を入れて、シートを選択してから実行すると問題なく動くのですが、何故このようなことが起こるのでしょう？

回答No.1

maruru01 · ベストアンサー

こんにちは。maruru01です。

ソースを見ただけですが。
Cells()にもちゃんとシートの指定をしないと、現在選択されているシートのセルになってしまいますよ。

SH1.Range(Cells(1, 1), Cells(D, E)).Value
↓
SH1.Range(SH1.Cells(1, 1), SH1.Cells(D, E)).Value

あと、質問の問題とは関係ないと思いますが、変数の宣言で、

Dim SH1, SH2, SH3, SH4, SH5, SH6 As Worksheet

とすると、Worksheetで宣言されるのは最後のSH6だけで、残りはVariantになります。
ちゃんと、

Dim SH1 As Worksheet
Dim SH2 As Worksheet
・・・・・
Dim SH6 As Worksheet

と、全部型を付けて宣言しましょう。

お礼

早速のご回答ありがとうございます。
自分で解決しようと四苦八苦していたのですが、このような形でCellsを使ったのが初めてなもので、全く気づきませんでした。思い切って聞いてみてよかったです。
また、変数の宣言も、今までこれで良いと思っていたので、ちょっとショックです。いつもありがとうございます。