別ブックへのデータ貼り付け処理でインデックスエラーが発生(VBA)

Question

前提・実現したいこと

VBAで、定型の複数のデータファイルから特定の複数のセルを抽出し、
出力用のテンプレートファイルに抽出結果を貼り付ける仕組みを構築しています。
(後続でDBにインサートする為の、事前のデータクレンジング機能の構築）

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

抽出結果を出力用テンプレートに貼り付ける部分のソースコードで以下のエラーメッセージが出力されます。

インデックスが有効範囲にありません

該当のソースコード

    Workbooks(Target_BookName).Worksheets(Sheet_Name).Range("L2").Select
    Selection.Copy
    Workbooks(Template_ExcelName).Sheets("emg").Activate　　　←　この行でエラーが出ます。
    Range("A2").Paste
    Application.CutCopyMode = False

※デバッグしましたが、Template_ExcelNameには値が正しく渡されています。

試したこと

とあるサイトで、「エクスプローラーのオプション」にて、「登録されている拡張子は表示しない」にチェックを入れれば解決するとあったので、
試したのですが解決できませんでした。

どうかご教示ください。

Answer 1

Select、Activate を使うのをやめる、つまり、対象がアクティブであることの前提のコードをかかない。
操作対象オブジェクトは変数に格納して使用するか、Withステートメントとを使って明示的に指定する。

上記の方針でコーディングすると、読みやすく、バグが発生しにくく、発生しても原因を特定しやすいコードになります。

今回のコードなら、例えば、下記のようになります。

Dim Target_Book As WorkBook
Set Target_Book = Workbooks("Target.xlsx")
Dim Target_Sheet As WorkSheet
Set Target_Sheet = Target_Book.WorkSheets("TargetSheet")

Dim Template_Book AS WorkBook
Set Template_Book = Workbooks("Template.xlsx")
Dim Template_Sheet As WorkSheet
Set Template_Sheet = Template_Book.WorkSheets("emg")

Target_Sheet.Range("L2").Copy Destination:=Template_Sheet.Range("A2")

ブック名やシート名が存在しないものなら、その行でエラーか出ますので、原因の特定がしやすいです。

Answer 2

以下のようにWorkSheetsに変更するとどうなりますでしょうか。

Workbooks(Template_ExcelName).WorkSheets("emg").Activate
'Workbooks(Template_ExcelName).Sheets("emg").Activate

Comments

[sawako.a]

回答ありがとうございます。
ご教示いただいた内容でも、変わらずインデックスエラーが出ます。

[TanakaHiroaki]

Sheets("emg")は、開いているWorkbooks(Template_ExcelName)に実在するシートで間違いないでしょうか？

[sawako.a]

間違いないです。

[TanakaHiroaki]

2行にしてみて、インデックスエラーは2行目で発生しますか？
Workbooks(Template_ExcelName).Activate
Worksheets("emg").Activate

[TanakaHiroaki]

ついでに以下も試してみてください。emgが変数なら、こちらが正しい記述です。
Workbooks(Template_ExcelName).Activate
Worksheets(emg).Activate

[sawako.a]

emgは変数ではなく、実シート名です。
2019/06/23 23:08のソースを試しましたが、１行目で発生します。

[TanakaHiroaki]

ありがとうございます。そうであれば
Workbooks(Template_ExcelName)が
別のExcelで開かれている可能性があります。

[sawako.a]

理解できていなくて申し訳ないのですが、
別のExcelで開かれているというのはどういう意味ですか？

[TanakaHiroaki]

例えば、Excel2010であれば、Excelを重複起動できます。
現在の問題は、開いているExcelファイルを認識できていないので、そのファイルはExcelを別に起動し、そこから開かれている可能性があるので、そのようにコメントしました。
もしそうでなければ、ファイル名の指定　Template_ExcelName　に誤りがあることになります。

[TanakaHiroaki]

ファイル名　Template_ExcelName　は、拡張子を含めて指定されていますでしょうか？