新規ブックを作成したものの、新規ブックへのコピペが上手くできません

VBAの新規ブックの作成とコピぺについて

最近VBAを学習し始め、ネット上のVBAの問題に取り組んでいます。
今回質問させていただくのは、
「Sheet1」のA1:C5のセル範囲を、新規シートのA1:C5にコピーする
という問題で、コピペが上手く作動しないエラーです。

発生している問題・エラーメッセージ

実行時エラー'9':
インデックスが有効範囲にありません

該当のソースコード

Sub ページの追加()

Dim 新規シート As Worksheet

Set 新規シート = Sheets.Add

新規シート.Name = "コピー先"

End Sub

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

Sheets("Sheet1").Range("A1:C5").Copy Sheets("コピー先").Range("A1")

End Sub

試したこと

2枚目のシートをExcel上から追加した上で「セルのコピー」を実施したところ正しく動作が行われました。

また、いくつかサイトを調べて新規シートの追加⇨コピペのコードは出てきました。

ただ、始めたばかりの自分にはどうしてこのコードではダメなのかが分からず頭を抱えています。
新規ブックも作成してあるのに、なぜ対象のものが無いというこのエラーが出てくるのかわかりません。

初期の質問で申し訳ありませんが、ご回答いただけると幸いです。
よろしくお願いいたします。

xail2222

2021/05/03 11:01

どういう状況で「セルのコピー」がエラーなのか質問文からは、私には読み取れません。「新規ブックも作成してあるのに」とはどういう意味ですか？マクロを実行しているファイルとは、別の新規ブックを作成しているという意味でしょうか。そして、その別の新規ブックに"コピー先"というシートを作っているということでしょうか？まぁ。推測なのでよくわかりませんが、どうなのでしょうか。

stevie

2021/05/04 06:57

xail2222 様コメント頂きありがとうございます。エラーメッセージが出てデバッグを押したところ Sheets("Sheet1").Range("A1:C5").Copy Sheets("コピー先").Range("A1") の部分に黄色いマーカーが引かれたので、「セルのコピー」部分の構文がエラーなのかと思いました新規ブックではなく新規シートでした、すみません！先に新規シートを作成して「コピー先」という名前をつけてあります。

xail2222

2021/05/04 07:12 編集

なるほど。「実行時エラー'9':インデックスが有効範囲にありません」なのだから手作業で作成したシートのシート名とコードのシート名に差異があるのかもしれませんね。シートのタブを選択し、右クリックで名前の変更をしてその名前をコピーして、プログラムの方に張り付けても改善しないでしょうか。 Sheet1とコピー先の両方。手作業でやると、名前の不一致によるシート名の不一致というのがよく見かけます。　ハイフンの記号が違っていたり、シート名の後ろにスペースがあったり… 後は Dim tSheet As Worksheet For Each tSheet In Sheets Debug.Print "[" & tSheet.Name & "]" Next を実行してみて、その結果を確認するというのも手としてはあるでしょうか。何にせよ、シート名をコピーして実行するか、シート名を変更してみて上手く行かないかを確認してみては如何でしょうか。名前を変更しても、コピーしても上手く行かなければまたコメントか何かお願いします。

xail2222

2021/05/04 07:30

後、別のExcelファイルを開いていてアクティブになっていてもエラーになるので注意してくださいね。なので、そういう危険を防ぐためには ThisWorkbook.Sheets("Sheet1").Range("A1:C5").Copy ThisWorkbook.Sheets("コピー先").Range("A1") とやった方が良いコードと言えますね。

stevie

2021/05/04 08:53

xail2222 様とても丁寧にご説明頂きありがとうございます！ Dim tSheet As Worksheet For Each tSheet In Sheets Debug.Print "[" & tSheet.Name & "]" Next を実行してみたところ｛Sheet1｝という結果が出ました。またご指摘頂いた通り、「Sheet1」と「コピー先」をコピペして実行してみたのですが、同じエラーメッセージが出てしまったので、Excelを再起動して、コピーしておいたコードを ThisWorkbook.Sheets("Sheet1").Range("A1:C5").Copy ThisWorkbook.Sheets("コピー先").Range("A1") に変更してペーストし、実行してみました。そうしましたら「マクロ」というウィンドウで、「セルのコピー」と「ページの追加」で別々に実行するものが表示され、別々に実行すると成功しました。ただ、一応確認をと思い、同じようにコピペをしながら自力で入力してみたところ、再び９のエラーメッセージが表示されてしまいました…(･･;) この後に何度かコードを同じように書き直したり、sheetsをworksheetsにしたりして実行してみたのですが、やはりエラーメッセージが出てきてしまいます。

xail2222

2021/05/04 09:08 編集

まず＞実行してみたところ｛Sheet1｝という結果が出ました。ということは「コピー先」のシートがないということです。この状態では質問に記載されたエラーが発生します。＞そうしましたら「マクロ」というウィンドウで、「セルのコピー」と「ページの追加」で＞別々に実行するものが表示され、別々に実行すると成功しました。そうですね。「ページの追加」で確実に「コピー先」が作成されるのでその後に「セルのコピー」を実行すると成功すると思います。＞同じようにコピペをしながら自力で入力してみたところ…やはりエラーメッセージが出てきてしまいます。自力でコピー先のシートを作成した後にシート名の一覧を出力するコードを実行するとどうなりますか？ "[Sheet1]"と"[コピー先]"と出力されるなら、コピーも動くはずなのですが、、、もし"[Sheet1]"と"[コピー先]"と出力されないなら、シートの作成がマクロのファイルに作成されていないことになります。別のExcelファイルにコピー先を作成しても、現状のコードではエラーとなります。マクロを記載したファイルにシートを追加していますでしょうか。

stevie

2021/05/04 09:37

>自力でコピー先のシートを作成した後にシート名の一覧を出力するコードを実行するとどうなりますか？自力でシート（コピー先）を作成して先程のコードを実行したところ、｛Sheet1｝と｛コピー先｝と出てきました！ >マクロを記載したファイルにシートを追加していますでしょうか。この確認方法で合っているのかはわからないのですが、2つ目のセルをコピーするコードを消して1つ目のコードを実行してみたところ、Sheet1と同じところにコピー先というシートが作られました

xail2222

2021/05/04 09:57

VBAのコードを見る所のプロジェクト - VBA Projectというウィンドウにおいて Sheet1とコピー先というシートが出来たのであればそのプロジェクトの標準モジュールや、Sheet1、またはコピー先のシートのプログラムとして Thisworkbook.Sheets("Sheet1").Range("A1:C5").Copy Thisworkbook.Sheets("コピー先").Range("A1") を書けば実行できると思うのですが… エラーなのですよね…

stevie

2021/05/04 09:58

xail2222　様 ------------------------------------------------------------------------------------ Sub ページの追加() Dim 新規シート As Worksheet Set 新規シート = Sheets.Add 新規シート.Name = "コピー先" ThisWorkbook.Sheets("Sheet1").Range("A1:C5").Copy ThisWorkbook.Sheets("コピー先").Range("A1") End Sub ------------------------------------------------------------------------------------ で無事実行できました… サブジェクトを別々に分けていたのが主な原因だったようです…????‍♀️ 長い時間を頂戴し、ご丁寧に考えてくださりありがとうございました（ ; ; ）

xail2222

2021/05/04 10:06 編集

＞サブジェクトを別々に分けていたのが主な原因だったようです…????‍♀️ うーん。まぁ。。それで出来ると言えば出来るのでしょうけれども手作業で出来ないのが、どうしてなのかは、謎なままですね。違うブックに作成していたのではないかとか思ったりしてるのですが。。。どうなのでしょう。まぁ。解決として満足したのなら、もう気にしなくてもいいかもしれないですね。＞長い時間を頂戴し、ご丁寧に考えてくださりありがとうございました（ ; ; ）いえいえ、こういうやり取りは私の楽しみでもありますので。とりあえず、解決と考えているのであれば自己解決に記載ですね。