【エクセルVBA】「インデックスが有効範囲にありません」というエラーがでます

質問

こんにちは。VBA初心者ですが、下記コードを実行すると
エラーが出てしまいました。

シート（シート1）のセルから別シート（シート2）のセルに
値のみコピーするVBAを組んでいます。
適宜、シート1に入力したものを読みこませていきたいので
変数を使用しています。

-------------------------------------------------------------
Sub セルのコピー()

Dim X As Integer

X = 3
Y = 2
Do While Cells(X, "A").Value <> ""
Sheets("シート1").Cells(X, "A").Copy

Sheets("シート2").Activate
Sheets("シート2").Cells(Y, "F").PasteSpecial _　Paste:=xlPasteValues　⇒エラー対象
Application.CutCopyMode = False

X = X + 1
Y = Y + 1

Loop

End Sub

------------------------------------------------------------
つまり、シート1のA3セルを先頭にA4,A5,A6・・・と続くセルの値
をシート2のF2を先頭としたセル（以下、F3,F4・・・）に値のみコピー
していきたいのですが。。

実行すると「インデックスが有効範囲にありません」というエラーが
でます。デバック対象は上記、「⇒エラー対象」の構文です。

変数の設定の仕方がおかしいのでしょうか。。
ご教示のほどよろしくお願いいたします。

回答No.3

KonnaMonde · ベストアンサー

>Sheets("シート2").Cells(Y, "F").PasteSpecial _　Paste:=xlPasteValues　⇒エラー対象
で、「PasteSpecial _　Paste:=xlPasteValues」　の　“_”　を消去してください。
また、
　Sheets("シート2").Activate　で　"シート2"　を　Activate　にすると、 Do　Loop　の頭に返ってきたとき、
　Do While Cells(X, "A").Value <> ""　の　Cells(X, "A")　は　"シート2"　の　Cells(X, "A")　を見に行きますので、　思っているような動作はしません。
　最後の　Loop の前に、　Sheets("シート1").Activate　を入れるか、While　の後の　Cells(X, "A").Value　の頭に、Sheets("シート1").　をつければ、この問題を回避することができます。

コピー先のシートは、アクティブにしなくても、ペーストする方法があります。

あなたのコードを少し変更します。

　Sub セルのコピー2()
　　　Dim X As Integer, Y As Integer

　　　X = 3
　　　Y = 2
　　　Do While Cells(X, "A").Value <> ""
　　　　　Sheets("シート1").Cells(X, "A").Copy Destination:=Sheets("シート2").Cells(Y, "F")
　　　　　X = X + 1
　　　　　Y = Y + 1
　　　Loop
　　　Application.CutCopyMode = False
　End Sub

注) 使っているブラウザの見る条件によって、「Sheets("シート1").Cells(X, "A").Copy Destination:=Sheets("シート2").Cells(Y, "F")」と「Destination:=Sheets("シート2").Cells(Y, "F")」が２行になったように見えることがあるかもしれません。　２つのコードはスペースを1つはさみ、続けて書いてください。

変更したコードの場合、"シート1" が常時表示されたままになります。

行数が２００行くらいとのこと、速度を早くしたい場合は、最初に
　application.ScreenUpdating=False
最後に、
　application.ScreenUpdating=True
を加えます。

なお、シート名は "Sheet1" ですか、"シート1"ですか、どちらかに揃えましょう。　、"シート1"と、"シート2"がブックの先頭から順になっているときは、シート名を使わなくてそれぞれ　Sheets(1)、Sheets(2)　でもいけます。　(1)、(2)　は左から　１番目のシート、２番目のシートという意味です。

試してみてください。

お礼

>konnamondeさま

ご回答ありがとうございます。
返信がおそくなり申し訳ありません。

シート名を誤転記していたのと、Do while cells(X,"A")の前にsheets("シート1")を追加、sheets("シート2").activateを削除したら、無事動作しました。

また、ScreenUpdatingプロパティを加えてみたら各段に動作が速くなり
感動しております。。

Sheets("シート2").Activate　で　"シート2"　を　Activate　にすると、 Do　Loop　の頭に返ってきたとき（以下省略）
⇒なるほど！でした。動作イメージがしっかり分かってないと。。ですね。

シート名を使わなくてそれぞれ　Sheets(1)、Sheets(2)　でもいけます
⇒こちらの方が使いやすそうですね。取り入れてみます！