エクセルＶＢＡでのフィルタオプションについて

質問

マクロの記録を利用して、自分なりに本を参考に以下のように手直しをしてみました。
やりたいこととしては、
名前の定義で“仕入単価他”とつけてあるデータから
ｓｈｅｅｔ1のＢ列に入力した内容(抽出条件）を
ｓｈｅｅｔ２に抽出するということです。

ｓｈｅｅｔ１の抽出条件はＢ列に入力します。
フィルタオプションの“ＯＲ”のようになり、
抽出する条件は複数行です。(列はＢ列のみ）

以下のようなコードで実行をすると、
Ｂ列の一番最初に書いたものの内容を抽出してくるだけで、複数のデータを引っ張ってきてくれません。
いろいろと直してはみたのですが、どうしても最初の条件のみを見て抽出してしまいます。
どのように手直ししてよいのかわからなくなってしまいましたので、教えてください。

Dim 検索 As Range
Dim 範囲 As Range

Set 検索 = Worksheets(1).Range("B1").CurrentRegion
Set 範囲 = Worksheets(1).Range("B65536").End(xlUp).Offset(5)
Worksheets(1).Activate
Range("仕入単価他").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=検索, _
CopyToRange:=Range("B65536").End(xlUp).Offset(5), Unique:=False

Range("B65536").End(xlUp).CurrentRegion.Copy _
Destination:=Worksheets(2).Range("A1")

End Sub

回答No.1

Wendy02 · ベストアンサー

>ｓｈｅｅｔ２に抽出するということです。
でしたら、直接、CopyToRangeに書き込んでください。
私自身、今回、ご質問で書かれたものが、なぜか、誤動作するか、はっきりしませんので、抽出先を一旦、Clear（ClearContentsでも可）してみました。
(私は、良くAdvancedFilterを使いますが、こういう場面に出会ったことがありませんでした。）

私自身の書き方は、データ部分も、検索=Criteria部分は、マクロでは取らないようにしています。名前で最初から指定してしまいます。

Sub test()
　Dim 検索 As Range
　'Dim 範囲 As Range
　With Worksheets(1)
　.Activate
　Worksheets(2).Range("A1").CurrentRegion.Clear
　Set 検索 = .Range("B1").CurrentRegion
　Range("仕入単価他").AdvancedFilter _
　Action:=xlFilterCopy, _
　CriteriaRange:=検索, _
　CopyToRange:=Worksheets(2).Range("A1"), _
　Unique:=False
　End With
　Set 検索 = Nothing
End Sub

お礼

アドバイスありがとうございます。
できれば、私も直接Ｓｈｅｅｔ２へ出したかったのですが、マクロの記録を元にしているため、どうしても直接別シートが指定できずに困っていました。
教えていただいたものでは、直接何の問題もなくＳｈｅｅｔ２へ抽出結果が出たのでこちらをコピーさせていただいて使用させていただきます。
なかなか変数やＷｉｔｈステートメントが理解できずに苦労しておりますので、
また御世話になりますが、よろしくお願いします。