VBAにて、複数シートからデータを抽出

質問

エクセルVBAです。複数のシートからの集計、抽出の書き方について教えてください。
1つのファイルに５０ほどのシートがあります。
各シートの列数やフォーマットは、同じですが行数は、異なります。

例

sheet１(シート名：集計）：集計用
　　A　 　　B 　　　C 　　　　　　D 　　　
1　※検索キーワードを入れるセルや
2　　 マクロを登録するボタン用として2行開けてある。
3 番号　氏名　　郵便番号　　住所　

sheet2(シート名：STU）
　　A　 　　B 　　　C 　　　　　　D 　　　　
1 番号　氏名　　郵便番号　　住所　
2　　1　　AB　　　３４５　　　　YZ
3　　1　　CD　　　６７８　　　　QS

sheet3(シート名：XYZ）
　　A　 　　B 　　　C 　　　　　　D 　　　　
1 番号　氏名　　郵便番号　　住所　
2　　2　　AB　　　３４５　　　　YZ
3　　2　　CD　　　６７８　　　　QS
4　　3　　CD　　　６７８　　　　QZ

抽出前は、上記の様なファイルとなっております。
上記では、4列としてますが実際は、23列あります。
また、sheet3までですが、実際は、40～100シート位あります。

sheet１(シート名：集計）：集計用
　　A　 　　B 　　　C 　　　　　　D 　　　
1　　　　　※検索キーワード：氏名_CD としマクロを実行する　
2　　　　　　　(↑例として氏名でフィルタリングしてますが他の指定項目でも実行したい、複数条件は、無)　　
3 番号　氏名　　郵便番号　　住所
4　　1　　CD　　　６７８　　　　QS　　　　(←sheet2(シート名：STU）の3列目)
5 　2　　CD　　　６７８　　　　QS 　(←sheet3(シート名：XYZ）の3列目)
6　　3　　CD　　　６７８　　　　QZ 　(←sheet3(シート名：XYZ）の4列目)
・ 　・　　CD　　　　・　　　　　　・ (←sheet4(シート名：・・・）の・列目)
・ 　・　　CD　　　　・　　　　　　・ (←sheet10(シート名：・・・）の・列目)
・　・　　CD　　　　・　　　　　　・ (←sheet27(シート名：・・・）の・列目)
・　 ・　　CD　　　　・　　　　　　・ (←sheet27(シート名：・・・）の・列目)
・ ・　　CD　　　　・　　　　　　・ (←sheet30(シート名：・・・）の・列目)

上記の様にすべてのシートから氏名：CDでフィルタリングし集計シートに抽出したい。

よろしくお願いいたします。

回答No.2

keithin · ベストアンサー

Ｂ１等にCDのように該当する列の検索したい言葉を記入することにして。

sub macro1()
　dim i as long
　dim h as range

　worksheets("Sheet1").select
　range(range("A4"), cells.specialcells(xlcelltypelastcell)).entirerow.delete

　on error resume next
　for i = 2 to worksheets.count
　　for each h in range("1:1").specialcells(xlcelltypeconstants)
　　worksheets(i).range("A1").currentregion.autofilter field:=h.column, criteria1:=h.value
　　next
　　worksheets(i).autofilter.range.offset(1).copy destination:=range("A65536").end(xlup).offset(1)
　　worksheets(i).autofiltermode = false
　next i
end sub

お礼

ありがとうございました。
ほぼ、やりたい事が出来ました。
頂いたコードをベースに完成させたいと思います。

回答No.1

米沢 栄蔵（@YON56）

抽出の書き方とは、
シートの枚数A,全シートのうちデータ取得したい最大行数をB,全シートのうちデータ取得したい最大列数をCと考えたとき、
取得したデータの保管装置を設計するということと考えます。

Dim AAA()

A=Sheets.Count
B=10000 (事前に調査。将来のことも考慮に入れて若干多めに設定)
C=23 (今回は4です)

ReDim AAA(A,B,C)

データの取得方法
For X=1 To A
Sheets((X).Select
D=Application.WorksheetFunction.Max(Range("A1:A10000"))
(データ取得したい行数をシート毎に取得する方法は、上記方法でなくても可)
For Y=1 To D
M=Y(データ取得したい行番号を取得する数式)
For Z=1 To C
N=Z(データ取得したい番号を取得する数式)
　　　　AAA(X,Y,Z)=Cells(M,N).Value
Next Z
Next Y
Next X

AAA(A,B,C)に全データが取得されています。

この取得したデータにて、計算したりその結果を表示する方法はご自身でお考え下さい。

お礼

ありがとうございます。
フィルタリング等をマクロ記録なりで勉強して
みます。