Workbook.BeforeSave イベントだとエラーが出る原因？

ざっくりですが、こんなマクロを作成しました。
------------
A.xlsm にてマクロを実行
　↓
A.xlsm の中にあるシートから特定の語句を含むシートを探す
　↓
該当シートから特定キーワードと一致するセルを探す
　↓
そのセルを起点とした範囲を設定し、オートフィルタでnull行を除外
　↓
★B.xlsx を開いて、A.xlsm で設定した範囲をコピーし値で貼り付け
　↓
B.xlsx を強制上書き保存で閉じる
　↓
A.xlsm を閉じて終了
------------
実際には、A.xlsm を保存した時、バックグラウンドで自動的に実行させたい処理ですが、
一旦は標準モジュールで開発しました。(何度も実行を繰り返してデバッグするため)

標準モジュールでは完璧に完成したつもりです。
A.xlsm を保存した時に実行するために、「Workbook.BeforeSave イベント」にソースを移したのですが、上記★の箇所で行き詰ってしまいました。※もちろん書き出しは「Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)」にしてあります。

ピンポイントですがソースはこれです。
------------
Workbooks.Open Filename:=FilePath & "B.xlsx"
Windows("B.xlsx").Activate
------------
「実行時エラー9：インデックスが有効範囲にありません。」で止まってしまいます。
やりたかった処理は、
B.xlsx をOpenして、B.xlsx を表に出してから、B.xlsx の目的のシートを表示させて一旦クリアして、
A.xlsm の範囲を B.xlsx に値で貼り付け・・・です。

Windows("B.xlsx").Activate を省いてしまうと、A.xlsm のシートをクリアしてしまいました。

何故標準モジュール通りに動かないのか、
どう修正したら良いのか、
お知恵を貸して頂けると幸いです。

※読み解くためにもっと広範囲のソースを出す必要があれば出します。

質問者からの補足コメント

• 

  すみません。お礼に書いた後に分かった事を補足で書かせていただきす。
  
  Cancel = True
  にしてみました。
  上書き保存(フロッピーマークのアイコン)を押すと、意図通りの動作をしました。
  
  Excelの右上の閉じるボタンを押すと、
  「〇〇〇〇.xlsm の変更内容を保存しますか？
  　[保存]　[保存しない]　[キャンセル]　」
  と聞かれた時、「保存」をクリックした場合は、マクロは実行されませんでした。
  [保存]の時はマクロ実行、
  [保存しない]の時はマクロも実行せずに閉じる。
  という動作は可能でしょうか？

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/11/10 14:30

• 

  お礼に書いた後にもう少し考えてみました。
  
  最初に表示される↓以下は手動で作った"Sub_C"ですね？
  「〇〇〇〇.xlsm の変更内容を保存しますか？
  　[保存]　[保存しない]　[キャンセル]　」
  
  2回目に表示される方が、エクセルを閉じるときに聞かれる画面だと思いました。
  
  ・・・となると2度聞かれるのも違和感があるので考えたのですが、
  方針変更で申し訳ありません。
  
  ファイルを閉じようとしたら必ず"Sub_B"を実行させてて、
  通常通りの
  「〇〇〇〇.xlsm の変更内容を保存しますか？
  　[保存]　[保存しない]　[キャンセル]　」
  にできませんか？
  
  [保存]　[保存しない]　[キャンセル]のどれを押すかには左右されない仕様でも良いと思いました。

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/11/11 10:29

• 

  何度も失礼しました。
  > [保存]　[保存しない]　[キャンセル] と
  > [はい]　[いいえ]　[キャンセル] の違いは気付いていますか？
  
  改めて確認しました。
  「[はい]　[いいえ]　[キャンセル]」は閉じようとした時に、
  マクロで表示させてますね。
  
  それで、「 [保存]　[保存しない]　[キャンセル] 」はExcelが出してる表示ですね。
  今、区別いたしました。

  No.4の回答に寄せられた補足コメントです。 補足日時：2021/11/11 17:59

No.3 ベストアンサー 回答者： よろずやkinchan 回答日時： 2021/11/10 21:20

＞[保存]の時はマクロ実行

標準モジュールに
Public saveflg As Boolean
Public quitflg As Boolean

Sub Sub_B()
saveflg = True
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "B.xlsx"
ThisWorkbook.Worksheets(1).Range("B1").Copy Destination:=ActiveSheet.Range("A1")
ActiveWorkbook.Close True
ThisWorkbook.Save
End Sub

Sub Sub_C()
If ThisWorkbook.Saved = False Then
Select Case MsgBox("'" & ThisWorkbook.Name & "' の変更内容を保存しますか？", vbYesNoCancel, Application.Name)
Case vbYes
Call Sub_B
Case vbNo
ThisWorkbook.Saved = True
Case vbCancel
Exit Sub
End Select
End If
Dim wb As Workbook
Dim cnt As Long
For Each wb In Application.Workbooks
If wb.Windows(1).Visible Then cnt = cnt + 1
Next wb
quitflg = True
If cnt = 1 Then
Application.quit
Else
ThisWorkbook.Close
End If
End Sub

ThisWorkbookモジュールに
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If quitflg Then
quitflg = False
Else
Cancel = True
Application.OnTime Now(), "Sub_C"
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If saveflg Then
saveflg = False
Else
Cancel = True
Application.OnTime Now(), "Sub_B"
End If
End Sub

この回答へのお礼

ありがとうございます。
まだ理解できてませんが、試したところ動作しました！

一点だけ気になったのですが、
右上の閉じるボタンを押すと、

「〇〇〇〇.xlsm の変更内容を保存しますか？
　[保存]　[保存しない]　[キャンセル]　」
と聞かれ、
[保存しない]　[キャンセル]いずれも自然な動作ですが、

[保存]を選択すると、意図通りのマクロが実行された後、
再度、
「〇〇〇〇.xlsm の変更内容を保存しますか？
　[保存]　[保存しない]　[キャンセル]　」
と聞かれてしまいます。
ここで[保存]を押すと実際に閉じてくれるのですが、
2度聞かれない方が違和感がありません。
最初に[保存]を押したら、マクロ実行後、自動的に保存して閉じてもらえると良いのですが可能でしょうか？
わがまま言ってすみません。

お礼日時：2021/11/11 09:46

No.5 回答者： よろずやkinchan 回答日時： 2021/11/11 17:31

＞以下の3行が、本来やりたい処理である100行位に差し替えてます。

差し替える前と後で動きを比べてみてください。

この回答へのお礼

承知いたしました。
ありがとうございます！

お礼日時：2021/11/11 17:34

No.4 回答者： よろずやkinchan 回答日時： 2021/11/11 11:33

こちらでのテストとはちょっと違うようなのですが？

[保存]　[保存しない]　[キャンセル] と
[はい]　[いいえ]　[キャンセル] の違いは気付いていますか？
アドインは何か使ってますか？
個人用マクロブックは使ってますか？
一字一句間違いなくコピペしましたか？
Excelのバージョンはどうなっていますか？

この回答へのお礼

こちら手違いがあったらすみません！

・今回は、二回とも「[保存]　[保存しない]　[キャンセル] 」が出てたと思います。
・アドインはPowerPivotを入れてたりしますが特に影響を及ぼす事はないんじゃないかと思います。
・個人用マクロブックは使ってません
・自力でソースを再現できる程理解できてないので(笑、範囲指定でコピペです。しかし、本来の処理はこのサンプルじゃないので貼り換えてます。
以下の3行が、本来やりたい処理である100行位に差し替えてます。
------------
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "B.xlsx"
ThisWorkbook.Worksheets(1).Range("B1").Copy Destination:=ActiveSheet.Range("A1")
------------
・Office365を使ってます。

お礼日時：2021/11/11 17:22

No.2 回答者： よろずやkinchan 回答日時： 2021/11/10 11:11

>「保存する前にSub_Bを実行しろ」とだけ記載して、

>標準モジュールの、Sub_B を実行させる。

Cancel = True で保存をキャンセルし、
OnTime で保存中の呪縛からのがれる、
ということです。

この回答へのお礼

ご提示のソースで実験してみたところ、
Workbook　BeforeSave の所に、
Cancel = True
Application.OnTime Now(), "Sub_B"
で、保存して開くと、ソースが以前のソースに戻ってしまうため、保存しておく事が出来ません。
どうしたら良いでしょうか？

お礼日時：2021/11/10 13:54

No.1 回答者： よろずやkinchan 回答日時： 2021/11/10 10:43

「Workbook.BeforeSave イベント」で出来ることは限られています。

そういうときの逃げ道にこんなのがあります。

標準モジュールに
Sub Sub_B()
Application.EnableEvents = False
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "B.xlsx"
ThisWorkbook.Worksheets(1).Range("A1").Copy Destination:=ActiveSheet.Range("A1")
ActiveWorkbook.Close True
ThisWorkbook.Save
Application.EnableEvents = True
End Sub

ThisWorkbookモジュールに
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.OnTime Now(), "Sub_B"
End Sub

この回答へのお礼

すみません、まだ初心者なのでご容赦ください。

このソースの理解は、
ユーザがファイルを保存した時のイベント内には、
「保存する前にSub_Bを実行しろ」とだけ記載して、

標準モジュールの、Sub_B を実行させる。
という理解で合ってますか？
合ってるならすごく応用が利きますよね！？

お礼日時：2021/11/10 10:47