ざっくりですが、こんなマクロを作成しました。
------------
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 のシートをクリアしてしまいました。
何故標準モジュール通りに動かないのか、
どう修正したら良いのか、
お知恵を貸して頂けると幸いです。
※読み解くためにもっと広範囲のソースを出す必要があれば出します。
No.3ベストアンサー
- 回答日時:
>[保存]の時はマクロ実行
標準モジュールに
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度聞かれない方が違和感がありません。
最初に[保存]を押したら、マクロ実行後、自動的に保存して閉じてもらえると良いのですが可能でしょうか?
わがまま言ってすみません。
No.4
- 回答日時:
こちらでのテストとはちょっと違うようなのですが?
[保存] [保存しない] [キャンセル] と
[はい] [いいえ] [キャンセル] の違いは気付いていますか?
アドインは何か使ってますか?
個人用マクロブックは使ってますか?
一字一句間違いなくコピペしましたか?
Excelのバージョンはどうなっていますか?
こちら手違いがあったらすみません!
・今回は、二回とも「[保存] [保存しない] [キャンセル] 」が出てたと思います。
・アドインはPowerPivotを入れてたりしますが特に影響を及ぼす事はないんじゃないかと思います。
・個人用マクロブックは使ってません
・自力でソースを再現できる程理解できてないので(笑、範囲指定でコピペです。しかし、本来の処理はこのサンプルじゃないので貼り換えてます。
以下の3行が、本来やりたい処理である100行位に差し替えてます。
------------
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "B.xlsx"
ThisWorkbook.Worksheets(1).Range("B1").Copy Destination:=ActiveSheet.Range("A1")
------------
・Office365を使ってます。
No.2
- 回答日時:
>「保存する前にSub_Bを実行しろ」とだけ記載して、
>標準モジュールの、Sub_B を実行させる。
Cancel = True で保存をキャンセルし、
OnTime で保存中の呪縛からのがれる、
ということです。
ご提示のソースで実験してみたところ、
Workbook BeforeSave の所に、
Cancel = True
Application.OnTime Now(), "Sub_B"
で、保存して開くと、ソースが以前のソースに戻ってしまうため、保存しておく事が出来ません。
どうしたら良いでしょうか?
No.1
- 回答日時:
「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 を実行させる。
という理解で合ってますか?
合ってるならすごく応用が利きますよね!?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2022/10/13 08:41
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 12:30
- Visual Basic(VBA) Excelのマクロコードについて教えてください。 1 2022/03/27 13:25
- Visual Basic(VBA) エクセルのマクロについて教えてください。 2 2023/03/12 10:10
- Visual Basic(VBA) エクセルのマクロについて教えてください。 4 2023/03/02 08:40
- PowerPoint(パワーポイント) エクセルのマクロについて教えてください。 1 2022/10/03 09:55
- Visual Basic(VBA) エクセルのマクロについて教えてください。 2 2023/06/04 09:39
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/03/07 14:05
- Excel(エクセル) ワードのマクロについて教えてください。 1 2023/03/11 13:50
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 11:27
このQ&Aを見た人はこんなQ&Aも見ています
-
風水の観点で選ぶ観葉植物とは?置き場所や上げたい運気ごとの注意点を紹介!
観葉植物で運気をアップするコツを、風水デザイン1級建築士の福島昌彦さんに伺った。
-
Excel VBA BeforeSaveが動作しないことについて
Excel(エクセル)
-
VBAでイベント処理に複数回入ってしまうのを防ぎたい
Excel(エクセル)
-
EXCEL VBA Workbook_BeforeSaveについて
Excel(エクセル)
-
-
4
有無、要否、賛否、是非、可否、当否…これらの言葉について
その他(教育・科学・学問)
-
5
VBAで「致命的なエラー」が出ました。どのくらい致命的なんでしょうか?
Access(アクセス)
-
6
エクセルでWorkbook_BeforeSaveイベントについての疑問
Excel(エクセル)
-
7
エクセルVBAでシートモジュールでのパブリック変数
Excel(エクセル)
-
8
エクセルVBAが途中で止まります
Visual Basic(VBA)
-
9
ExcelVBA実行後に時々落ちる
Visual Basic(VBA)
-
10
マクロの「SaveAs」でエラーが出るのを解消したいです(再)
Visual Basic(VBA)
-
11
エクセルのvbaにて thisworkbookに記載のマクロを他のブックにマクロにて自動コピー出来る
Excel(エクセル)
-
12
VBA public変数はどのようなことをしたら解放されますか?
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBA This Workbookモジュール...
-
VBでグローバル変数を宣言するには
-
VBAからpythonへの転向について
-
VBA モジュールで共通に使う変...
-
エクセルVBAでシートモジュール...
-
Apache2 静的・動的モジュール...
-
Excel VBA 定義されたプロージ...
-
Excel VBAで、ユーザーフォーム...
-
グラフのX,Y座標を取得したい
-
Excelで時刻になったら知らせて...
-
【vba】フォームに書いてあ...
-
VBAで旧字体を異字体に一括で変...
-
Excel VBA 『Call』で呼び出す...
-
標準モジュールにpublicで宣言...
-
マクロについて教えてください。
-
モジュールの独立性について
-
C#のアプリケを64bit版のOSで起...
-
ArduinoのジャイロモジュールMP...
-
フォームがアクティブになった...
-
Excel VBAでリンク切れをチェッ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel VBAでリンク切れをチェッ...
-
Excel VBA 定義されたプロージ...
-
ArduinoのジャイロモジュールMP...
-
ユーザー定義関数に#NAME?が返...
-
VBAで別モジュールへの変数の受...
-
エクセルVBAでシートモジュール...
-
Excel VBA 『Call』で呼び出す...
-
モジュールとは何ですか
-
VBA This Workbookモジュール...
-
VBでグローバル変数を宣言するには
-
グラフのX,Y座標を取得したい
-
VBAで旧字体を異字体に一括で変...
-
モジュールの最大数はいくつな...
-
【vba】フォームに書いてあ...
-
Excel VBAで、ユーザーフォーム...
-
Form間の値の渡し方
-
モジュールとクラスの違いって...
-
Access VBA標準モジュールにつ...
-
本当にPublicな変数(配列で)
-
標準モジュールを削除したい。(...
おすすめ情報
すみません。お礼に書いた後に分かった事を補足で書かせていただきす。
Cancel = True
にしてみました。
上書き保存(フロッピーマークのアイコン)を押すと、意図通りの動作をしました。
Excelの右上の閉じるボタンを押すと、
「〇〇〇〇.xlsm の変更内容を保存しますか?
[保存] [保存しない] [キャンセル] 」
と聞かれた時、「保存」をクリックした場合は、マクロは実行されませんでした。
[保存]の時はマクロ実行、
[保存しない]の時はマクロも実行せずに閉じる。
という動作は可能でしょうか?
お礼に書いた後にもう少し考えてみました。
最初に表示される↓以下は手動で作った"Sub_C"ですね?
「〇〇〇〇.xlsm の変更内容を保存しますか?
[保存] [保存しない] [キャンセル] 」
2回目に表示される方が、エクセルを閉じるときに聞かれる画面だと思いました。
・・・となると2度聞かれるのも違和感があるので考えたのですが、
方針変更で申し訳ありません。
ファイルを閉じようとしたら必ず"Sub_B"を実行させてて、
通常通りの
「〇〇〇〇.xlsm の変更内容を保存しますか?
[保存] [保存しない] [キャンセル] 」
にできませんか?
[保存] [保存しない] [キャンセル]のどれを押すかには左右されない仕様でも良いと思いました。
何度も失礼しました。
> [保存] [保存しない] [キャンセル] と
> [はい] [いいえ] [キャンセル] の違いは気付いていますか?
改めて確認しました。
「[はい] [いいえ] [キャンセル]」は閉じようとした時に、
マクロで表示させてますね。
それで、「 [保存] [保存しない] [キャンセル] 」はExcelが出してる表示ですね。
今、区別いたしました。