複数のブックを開いているとエラーが出てしまいます

質問

エクセルのマクロ初心者でたいへん恐縮です。質問の仕方が悪ければお詫び申し上げます。

　エクセルでそれぞれ違うマクロが記録された二つのブックを開いて、シートの表から抽出などの操作をしています。例えば、(1).xlsと　(2).xlsにはそれぞれ部品表のようなリストを含めて３つのシートがあります。その二つのブックを頻繁にアクティブに切り替えながら作業をしようとしているのですが、片方を使っていると別のブックを使った時に「インデックスが有効範囲にありません」というエラーが出て、アクティブブックとは違うシートを読み込んでしまっているようです。
ちなみにブックを一つだけ開いて操作している時は全くエラーはでません。二つのブックを扱っている時だけです。マクロはエンターを押すと実行されるようになっており、一方を実行するともう片方のブックでエラーが出てしまいます。
その二つのブックのコードでエラー(黄色く表示)になるのは以下の★～★の部分です。
------------------------------------------------------------------------------------
＜(1).xlsのマクロ＞
Sub Auto_Open()

' Auto_Open Macro

ActiveSheet.Unprotect
'
★Sheets("パッキンリスト").Columns("B:N").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B2:B3"), CopyToRange:=Range("検索出力表!Extract"), Unique _
:=False★
ActiveWindow.SmallScroll Down:=-28
Range("C19:N19").Select
Selection.Copy
Range("D3:E14").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
ActiveWindow.SmallScroll Down:=4
Rows("21:21").Select
Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

ActiveWindow.ScrollRow = 1
Range("B3").Select
Application.OnKey Key:="{RETURN}", Procedure:="Auto_Open"
Application.OnKey Key:="{ENTER}", Procedure:="Auto_Open"

ActiveSheet.Protect UserInterfaceOnly:=True

End Sub
-----------------------------------------------------------------------------------
＜(2).xlsのマクロ＞
Sub Auto_Open()

' Auto_Open Macro

ActiveSheet.Unprotect

If Range("B3").Cells = "" Then
MsgBox " 　☆ パーツNo,が未入力です。"
ActiveSheet.Protect UserInterfaceOnly:=True
Exit Sub
End If
★Sheets("パーツリスト").Columns("C:G").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B2:B3"), CopyToRange:=Range("B20:E20"), Unique:= _
True★
Range("C21:E21").Copy
Range("D3:E5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True

ActiveWindow.ScrollRow = 1
Application.OnKey Key:="{RETURN}", Procedure:="Auto_Open"
Application.OnKey Key:="{ENTER}", Procedure:="Auto_Open"

ActiveSheet.Protect UserInterfaceOnly:=True

End Sub
------------------------------------------------------------------------------------

　二つのブックを開いて、下のタスクバーでブックを切り替えて操作してもお互いが干渉せずそれぞれのブックのプログラムがエラー無く動くようにしたいのですが、
もし、何か解決法などございましたらご教授いただければ幸いです。
何卒よろしくお願い申し上げます。

回答No.1

keithin · ベストアンサー

アクティブブックを切り替えているのもありますが，それらのマクロの一番の問題点は，「Enterキーでマクロを動かさせる」の仕込みが甘いことにあります。
最低限の手直しに止めるにしても，表示ブックを切り替えるたびに，Enterしたときに「今はどのブックのマクロが起動すればいいのか」の設定を都度設定し直させないといけません。

手順：
VBE画面のプロジェクトエクスプローラで，（１）ブックのThisWorkbookをＷクリックして開く
現れたシートに下記をコピー貼り付ける

private sub Workbook_Activate()
application.onkey "{ENTER}", "Auto_Open"
application.onkey "{RETURN}", "Auto_Open"
end sub

private sub Workbook_Deactivate()
application.onkey "{ENTER}"
application.onkey "{RETURN}"
end sub

private sub Workbook_Close()
application.onkey "{ENTER}"
application.onkey "{RETURN}"
end sub

（２）ブックのThisWorkbookにも，同じマクロをコピー貼り付ける。
以上です。

＃補足
もうちょっとマクロのスキルが上がってから先のお話になりますが，将来的には今の「Enterキーで起動する」のマクロを全くやめて，各ブックの今作業しているシートそれぞれに，「Ｂ３セルに記入したら所定のマクロが自動で動く」というマクロに切り替えます。

練習：
まっさらのブックを用意する
シート１のシート名タブを右クリックしてコードの表示を選ぶ
現れたシートに下記をコピー貼り付ける

private sub Worksheet_Change(byval Target as excel.range)
if target.address <> "$B$3" then exit sub

msgbox thisworkbook.name & " B3 enter"
’実際にはこの続きに，データの抽出と転記の手順を記入していく
end sub

ブック２のシート１にも同じ仕込みをして，それぞれのブックを並べてＢ３に記入する動作をおこなってみる。

お礼

keithin様

おかげさまで教えて頂いた通り行いましたところ、問題なく動作いたしました。マクロ初心者であるにもかかわらず、コードや貼り付け方まで教えて頂きたいへん感謝申し上げます。
今回のエンターで実行させる方法の仕込みが甘さと考え方の方向性が甘いことを痛感しました。

あと、おっしゃるとおり今後はエンターによる実行ではなく、所定の記入したセルで自動実行させる考えに切り替えてみます。
そして、ご丁寧に付け加えて頂いたそのマクロの練習や勉強も同時に行って行きたいと思います。
　大変奥が深く、まだまだ勉強しなければいけないですが、これを機にがんばってみたいと思います。

末筆になりますが、このたびはたいへんご親切に分かりやすく教えていただき、本当に感謝いたします。直接お礼をしたいところですが、ここでのお礼とさせていただきます。
重ね重ね誠にありがとうございました。