Excel VBAで参照先の変換

質問

ExcelのVBAでBook1.xlsxのSheet2とSheet2を参照しているSheet3をBook2.xlsxにコピーするプログラムを作成しているのですが、Sheet3のコピーが思うように行きません。
具体的にはSheet3が参照しているSheet2をBook1.xlsxからBook2.xlsxに変換する所です。

下記のようなプログラムを作成しました。
strDirectory = ThisWorkbook.Path
strSrcBook = "Book1.xlsx"
Workbooks.Open Filename:=strDirectory & "\" & strSrcBook, ReadOnly:=True
Set wsSrc = Workbooks(strSrcBook).Worksheets("Sheet2")
Set wsDst = ThisWorkbook.Worksheets("Sheet1")
wsSrc.Copy After:=wsDst
Set wsSrc = Workbooks(strSrcBook).Worksheets("Sheet3")
Set wsDst = ThisWorkbook.Worksheets("Sheet2")
wsSrc.Copy After:=wsDst
wsDst.Range("D:D").Replace "[*]", ""
Workbooks(strSrcBook).Close SaveChanges:=False

Sheet3のD列がSheet2を参照しているのですが、上記を実行すると開くファイルの選択を要求され、キャンセルし続けると実行が完了しますが、コピーされたSheet3の参照先が正しく変換されずエラー表示となってしまいます。
「wsDst.Range("D:D").Replace "[*]", ""」が完了する前に「Workbooks(strSrcBook).Close SaveChanges:=False」が実行されてしまうのが原因と思われるのですが、実際のプログラムでは開くBookは1つではなくSheet1に記載したリストを順番に開いてコピーするという事を行っている為、作業が完了したBookは閉じるようにしたいです。
どの様に修正すれば「wsDst.Range("D:D").Replace "[*]", ""」が完了するのを待って、「Workbooks(strSrcBook).Close SaveChanges:=False」が実行されるように出来るのでしょうか?

回答No.1

real beatin（@realbeatin） · ベストアンサー

こんにちは。

理解が至っているか判りませんが、
常に２つのシートを、丸々シートごと、
自ブックにコピーする作業を繰り返す、ということだとして。

コピー先で外部ブックへの参照名を置換で消したとしても、
シート名がソースブックとデストブックで
必ず一致するとは限りませんよね。
　"Sheet2"　が既存であれば、"Sheet2 (2)" とか。
必ずしも、この点が原因でうまく行っていないという事でも
無いかも知れませんが、不確定要素を無理に抱えるような
処理方法という印象です。

シートふたつを纏めてコピーすれば、
シート間の参照関係もそのまま（自己完結するように）コピーできます。
なので、コピー後に参照先を変換する必要はないのでは？と。

ふたつを纏めてコピー。例えば、以下のように。

' ' //
' Dim colWsSrc As Sheets
　　strDirectory = ThisWorkbook.Path
　　strSrcBook = "Book1.xlsx"
　　Workbooks.Open Filename:=strDirectory & "\" & strSrcBook, ReadOnly:=True
　　Set colWsSrc = Workbooks(strSrcBook).Worksheets(Array("Sheet2", "Sheet3"))
　　Set wsDst = ThisWorkbook.Worksheets("Sheet1")
　　colWsSrc.Copy After:=ThisWorkbook.Worksheets("Sheet1")
　　Workbooks(strSrcBook).Close SaveChanges:=False
' ' //