マクロの動作するタイミング

質問

こんにちは。
VBAに関して初心者です。宜しくお願い致します。

sheet1のB1でsheet名の一覧をドロップダウンから選択できるようにし、
sheet1A列に、選択したsheetのA列が表示されるようにしました。
そのA列をオートフィルターで「空白以外のセル」を表示していますが、
これをマクロを使って、常にオートフィルターを適用した状態にしたく

----------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Selection.AutoFilter Field:=1, Criteria1:="<>"
Columns("A:A").Select
Selection.Copy
End Sub
----------------------------------------------------------

と、しました。
上記のマクロではB1で選択後、ダブルクリックするとA列の
オートフィルターを適用し、その列をコピーする。
と言うことになります。

これを、ダブルクリックするのではなく、B1でsheet名を選択すると
オートフィルターを適用するようにするには、どうしたらよいのでしょうか。

お願い致します。

WinXP Excel2003です。

回答No.1

ki-aaa · ベストアンサー

これでどうですか。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Application.EnableEvents = False
Sheets(Target.Value).Columns("A").Copy
Range("A1").PasteSpecial Paste:=xlPasteAll
Selection.AutoFilter Field:=1, Criteria1:="<>"
Columns("A:A").Select
Selection.Copy
Application.EnableEvents = True
End Sub

・・・
注
Application.EnableEvents = False
Application.EnableEvents = True
上のコードを使ったら、必ず、下のコードで
Trueにしないといけません。
そうしないと、イベントが発生しなくなります。

なぜ、Application.EnableEvents = Falseを使うかというと、コピーペーストや値の代入で、イベントが発生して、自分自身を呼び出そうとするからです。

補足

有難うございます。

上記の通り貼り付けましたが、上手く行きませんでした。
------------------------------------------
Sheets(Target.Value).Columns("A").Copy
Range("A1").PasteSpecial Paste:=xlPasteAll
------------------------------------------
この２行を除いて実行したら上手く行きました。
この２行はどういう意味か分かりませんが…。
勉強不足ですみません。

回答No.2

ki-aaa

補足、読ませてもらいました。

Target.Valueには、セル　Range("$B$1")の値が入ります。(If Target.Address <> "$B$1" Then Exit Subのコードが有るので)
質問文に、"sheet1のB1でsheet名の一覧をドロップダウンから選択できるようにし、"と有りましたので、B1が"シート2"と成っていたら
>Sheets(Target.Value).Columns("A").Copyは
Sheets("シート2").Columns("A:A").Copyと同じ意味になります。

つまり、セルの"B1"で指定したシートのA列をsheet1のA列に貼り付けています。

でわでわ