VBAのブックやシートのアクティブ化について質問です。

質問

元ファイルに格納されているマクロを実行すると、
他のエクセルファイルを開いて、
そこから必要な情報を収集して元ファイルに集める…
というマクロをいま組んでいるのですが、

他のエクセルファイルを開いたら、
そのファイルはアクティブになるはずで、
それ以下に記述の処理は、元のファイルを触らない限り、
当該他のファイルに対して行われると思っているのですが、
元ファイルが処理されていします。

なので、現在、記述する際には、
以下のようにブック名とシート名から指定しています。
Workbooks(BookBango & ".xls").Sheets(2).Range(Workbooks(BookBango & ".xls").Sheets(2).Cells(1, 1),
Workbooks(BookBango & ".xls").Sheets(2).Cells(paste_counter, 3)).Copy …

これで一応動くは動くのですが、
とても冗長ですし、ミスを誘いやすく、修正が大変ですので、
解決したいのですが、
どうにも解決法が分かりません。
不勉強で申し訳ありませんが、どうかご指導下さい。
お願いします。

回答No.3

onlyrom · ベストアンサー

＞他のエクセルファイルを開いたらそのファイルはアクティブになるはずで、
＞それ以下に記述の処理は、元のファイルを触らない限り当該他のファイルに対して行われると思っているのですが

まんまと落とし穴に嵌ってしまいましたね。(^^;;;

原因は、コードの書いてある場所にあります。

そのコードは、シートモジュールに書いてあり、
かつ、
Rangeオブジェクトの親（シート）オブジェクトが省ぶかれていますよね。
それでは質問のようになります。

シートオブジェクトを省いたRangeをシートモジュールに書くと、
他のブック（または同じブックの他のシート）をアクティブにしても
Rangeオブジェクトはコードのあるシートを摑んだままです。
即ち今回の場合は、元ブックのそのコードの書いてあるシートということになります。

なので、フツーは、既出の回答のようにオブジェクト変数を使ったりしますが、
シートオブジェクトを省いたRangeオブジェクトを使いたいということであれば
そのコードを【標準モジュール】に置き、シートからCallするようにします。
【標準モジュール】に置いておけば質問者が考えてるとおりになります。

これは一回でも経験したことがないと分からないことです。

例えば、元ブックのSheet1のCommandButtonをクリックして他のブックを開き
そのブックのSheet1のセルを元ブックのSheet1へコピーする場合

'-------シートモジュール-------------

Private Sub CommandButton1_Click()
　　Call CellCopy
End Sub

'-------標準モジュール------------------------

Sub CellCopy()
　　Workbooks.Open ThisWorkbook.Path & "\BBB.xls"
　　Range("A1:B5").Copy ThisWorkbook.Sheets("Sheet1").Range("B10")

　　　　＝＝その他の処理＝＝
End Sub
'-----------------------------------------------

とまあ、偉そうなことを書きましたが、
外していましたら、ご容赦願います！(^^;;;
以上。

お礼

標準モジュールを挿入して改めてやったら、
成功しました！！
こんな簡単なことに気づかなかったとはお恥ずかしい限りです。
適当な勉強は駄目ですね・・・しっかり勉強し直します。
ありがとうございました！！

回答No.2

nda23

No.1の方の指摘のように変数を使うべきです。

Dim WkBk As Workbook
Dim WkSt As WorkSheet
Dim Rng As Range

Set WkBk = Workbooks(BookBango & ".xls")
Set WkSt = WkBk.WorkSheets(2)
Set Rng = WkSt.Range(Cells(1, 1), Cells(paste_counter, 3))
または
Set Rng = WkSt.Range("A1:C" & Cstr(paste_counter))
Rng.Copy・・・

お礼

単に標準モジュールに記述していなかったための問題でした・・・
お恥ずかしい限りです。
ご回答ありがとうございました。

回答No.1

hige_082

With　～　End With　や　変数を使用するのが一般的だと思います

例として
'------------------------------------------
Workbooks(BookBango & ".xls").Sheets(2).Range("A1").Value="aaa"
MsgBox Workbooks(BookBango & ".xls").Sheets(2).Range("A1").Value
'------------------------------------------
のような処理があるとします
Withを使用すると
'------------------------------------------
With Workbooks(BookBango & ".xls").Sheets(2).Range("A1")
.Value="aaa"
MsgBox .Value
End With
'------------------------------------------
で済みます
また、次のような処理の場合は
'------------------------------------------
Workbooks("aaa.xls").Sheets("aaa").Range("A1").Value=Workbooks("bbb.xls").Sheets("bbb").Range("A1").Value
　　　　　　　　　・
　　　　　　　　　・
　　　　　　　　　・
Workbooks("aaa.xls").Sheets("aaa").Range("A10").Value=Workbooks("bbb.xls").Sheets("bbb").Range("A10").Value
'------------------------------------------
変数を使用するのが良いと思います
'------------------------------------------
Dim aaa As Object
Dim bbb As Object
Set aaa=Workbooks("aaa.xls").Sheets("aaa")
Set bbb=Workbooks("bbb.xls").Sheets("bbb")
aaa.Range("A1").Value=bbb.Range("A1").Value
　　　　　　　　　・
　　　　　　　　　・
　　　　　　　　　・
aaa.Range("A10").Value=bbb.Range("A10").Value
'------------------------------------------
のようにすれば、修正があった場合
例えば
aaa.xlsをccc.xlsに
bbb.xlsをddd.xlsに修正したい場合も
Set aaa=Workbooks("aaa.xls").Sheets("aaa")
Set bbb=Workbooks("bbb.xls").Sheets("bbb")
の部分を
Set aaa=Workbooks("ccc.xls").Sheets("aaa")
Set bbb=Workbooks("ddd.xls").Sheets("bbb")
で修正は終わりです
解り難い説明ですみません

お礼

単に標準モジュールに記述していなかったための問題でした・・・
お恥ずかしい限りです。
変数を扱う方法も初めて知りましたので、
今後使えそうだったら、是非使ってみたいと思います。
丁寧なご回答ありがとうございました！