excel マクロでデータ抽出したい

質問

excelの抽出をマクロ化しようと思っています。

キー記録で
Sheets("data").Range("B11:O714").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("data").Range("G3:G5"), CopyToRange:=_
Range("B4:O615") , Unique:=False
を得たので、これを元にして、条件範囲をオートシェイプのある列を条件にしようと思い、

col = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Column

で、オートシェイプの列を取得し、
Sheets("data").Range("B11:O714").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("data").Range(Cells(3, col), Cells(5, col)),_
CopyToRange:=Range("B4:O615") , Unique:=False
としたのですが、エラーになってしまいます。

colを使って条件範囲をするにはどうしたらいいのでしょうか？

よろしくお願いします。

回答No.3

imogasi · ベストアンサー

質問のコードは、うまく行かないことは、他の回答者の説明があります。
目的（データ抽出とか言う目的でなく、わかりやすく、手早く使用する（条件を整える）うえで、こういう風にして省力化的に、「しくみ」をしたいと、「文章で！！」説明して質問すべきでしょう。自分の（たぶん、そんなにＶＢＡのエキスパートでない質問者の）アイデアの先行・無理が露出した例だと思うが、よくある。そういう質問者は自作のＶＢＡコードを張り付けて、仕組みの目的はあまり説明しない質問者が多い。その仕組みと言うかアイデアこそ、ここで識者に尋ねるべきと思う。
（例）オートシェイプのある列を条件にしようと思い、・・
チェックを入れた列を条件列にするとかか。
一応ＶＢＡを利用するということは、それだけで、省力化になっているわけです。
今回は条件さえも、何とかマウスのクリック指定で何とかしようということか？
こういうユーザーインターフェースの部分までいじくるとなると、ＶＢＡを超えたスキルがいる場合が多いように思う。
ＶＢＡのエクスパートでなければ、他人が使うことまで考慮した仕組みを作るのは、難しいと思う。
エクセルは自分で使う用だと思う。
ーーー
内容は、お気に召さないかもしれないが、やってみた。
シートデータ例　Ｓｈｅｅｔ２のＡ２０：Ｉ３０　（ＷＥＢ例から借用した）
出席番号 氏名 国語 算数 理科 社会 合計 順位 評価
1001 佐藤 20 51 48 46 165 9 不可
1002 鈴木 56 64 67 59 246 5 良
1003 高橋 89 92 97 81 359 1 優
1004 田中 71 78 75 85 309 4 良
1005 渡辺 25 34 45 54 158 10 不可
1006 伊藤 48 56 42 52 198 7 可
1007 山本 92 88 84 76 340 2 優
1008 中村 84 89 76 84 333 3 良
1009 小林 61 59 65 54 239 6 可
1010 加藤 34 82 38 49 168 8 可
条件入力シートはＳｈｅｅｔ３　Ａ１：Ｉ６　（条件入力シートがデータシートと分離。これはできるようだ。

条件入力シート ＜－－第１行
出席番号 氏名 国語 算数 理科 社会 合計 順位 評価
佐藤
鈴木
中村
伊藤
氏名をほしいだけ、操作者が入力していく。
（１）直接入力以外では、（２）元シートのコピー貼り付け、（３）全員のある氏名列を用意し、そこからＣｔｒｌキーを押しながらＤ＆Ｄ、（４）ＬｉｓｔＢｏｘで選択、などの方法を思いつくが。
ーーー
ＶＢＡコード
標準モジュールに
Sub Adfilter3()
lr2 = Worksheets("Sheet2").Range("B15").End(xlDown).Row '元データ最下行
MsgBox lr2
lr3 = Worksheets("Sheet3").Range("B15").End(xlUp).Row '条件最下行
MsgBox lr3
Worksheets("Sheet2").Range("A20:I30").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Sheet3").Range("B2:I" & lr3), _
CopyToRange:=Worksheets("Sheet4").Range("A1:I50"), _
Unique:=False
'Ｓｈｅｅｔ４に抽出
End Sub
ーーー
結果
Ｓｈｅｅｔ４に　Ａ１：Ｉ５
出席番号 氏名 国語 算数 理科 社会 合計 順位 評価
1001 佐藤 20 51 48 46 165 9 不可
1002 鈴木 56 64 67 59 246 5 良
1006 伊藤 48 56 42 52 198 7 可
1008 中村 84 89 76 84 333 3 良
ーーー
ほかに、（氏名はイコール条件なのだが）未検討なのは、数値の大小条件です。
上記条件例で、Ｓｈｅｅｔ３の氏名列は空白とし、国語列のＣ２に>65などと入れる。
Ｓｈｅｅｔ3のＡ１：Ｉ３
条件入力シート
出席番号 氏名 国語 算数 理科 社会 合計 順位 評価
>65
と入れてＶＢＡを実行すると（６５点以上）
結果
出席番号 氏名 国語 算数 理科 社会 合計 順位 評価
1003 高橋 89 92 97 81 359 1 優
1004 田中 71 78 75 85 309 4 良
1007 山本 92 88 84 76 340 2 優
1008 中村 84 89 76 84 333 3 良
ーー
あと、Ｓｈｅｅｔ４で結果の表示列の選択（操作者が決定できるようにする）があるが、略。
ピヴォットテーブルのようにシート上でＤ＆Ｄを自由にやるスキルがないので上記どまりかな。

回答No.2

kagakusuki

ActiveSheet.Shapes(Application.Caller)

でオートシェイプを指定できるのは、そのオートシェイプに上記のマクロを登録していて、尚且つ、そのオートシェイプをクリックする事などによってマクロを起動させた場合に限ります。

>エラーになってしまいます。

という事は、「そのマクロを登録済みのオートシェイプをクリックする」という以外の方法で、そのマクロを起動させたからだと思われます。
　ですから、条件範囲を指定する方法として、もしオートシェイプをクリックするという方法を使うのであれば、それらのオートシェイプの全てに、そのマクロを登録しておかれると良いと思います。

　それに対し、もしそれ以外の方法でマクロを起動させるのであれば、

>条件範囲をオートシェイプのある列を条件にしようと思い、

という箇所に記述されている「オートシェイプ」とは、一体何のオートシェイプの事を指しているのかという事に関して、質問者様の御質問文中には何の説明もないため、このままでは回答のしようが御座いません。
　ですから、どのオートシェイプの事なのかという事に関して御説明願います。

回答No.1

FEX2053

まず、この式でちゃんと「条件範囲」を指示しているかどうかが問題です。

CriteriaRangeは、抽出条件の見出しと「100%整合した」見出しじゃ
ないと動作しませんよ。全角半角はおろか、空白の有無が違うだけでも
動作しなくなります。

もう一つ、CopyToRangeは、コピーした結果その範囲を超えちゃうと
エラーします。なので、"B4:O4"としておくのが普通です。