Excel VBA(エクセル マクロ) そのD オートフィルタをフィルタ部分(見えている部分)のみコピー

外部ファイルの取り込みが可能となった。
今度はオートフィルタをかけてフィルタされたデータのみ他ブックの他シートにコピーしよう。

外部からファイル取り込みができるようになり、今度はフィルタをかけて必要な部分のみ取り込むようにする。

まずは、オートフィルタを使用してみる。

With Workbooks(データ.xls).Worksheets(データ).Range("1:65536")
.AutoFilter 9, "*abc*"
.Copy Workbooks(データ1.xls).Worksheets("データ1").Range("A1")
.AutoFilter
End With

9列目にabcが含んだ行をデータ1.xlsにコピーする。

これは比較的に簡単にできた。

これでは、指定した列のみを並び替えて抽出できない。

そこでAdvancedFilterを使用することとした。

Dim sakiRng1 As Range, sakiRng2 As Range, sakiRng3 As Range
Dim myQry1 As Range, myQry2 As Range, myQry3 As Range

Set sakiRng11 = Workbooks(データ1.xls).Worksheets("データ1").Range("A1:B1:C1")
Set sakiRng12 = Workbooks(データ1.xls).Worksheets("データ1").Range("M1:N1")
Set sakiRng13 = Workbooks(データ1.xls).Worksheets("データ1").Range("R1:S1")

Set myQry1 = Workbooks(データ1.xls).Worksheets("用語").Range("B1:B2")

Sheets(データ1).Select
For j = 0 To 17
Cells(1, j + 1).Value = koumoku2(j)
Next

With Workbooks(データ.xls).Worksheets(データ).Range("A8:N500")
.AdvancedFilter xlFilterCopy, myQry1, CopyToRange:=sakiRng11
.AdvancedFilter xlFilterCopy, myQry1, CopyToRange:=sakiRng12
.AdvancedFilter xlFilterCopy, myQry1, CopyToRange:=sakiRng13
sakiRng11.CurrentRegion.EntireColumn.AutoFit
sakiRng12.CurrentRegion.EntireColumn.AutoFit
sakiRng13.CurrentRegion.EntireColumn.AutoFit
End With

一気に難しくなった。

まず、データ1.xlsの1行目に抽出したい列項目の題名を記載しておく。
Sheets(データ1).Select
For j = 0 To 17
Cells(1, j + 1).Value = koumoku2(j)
Next

データ1.xlsへ抽出したい列番号(A1等)のレンジをセットしておく。
データ型はAs Rangeとする。
Set sakiRng11 = Workbooks(データ1.xls).Worksheets("データ1").Range("A1:B1:C1")
Set sakiRng12 = Workbooks(データ1.xls).Worksheets("データ1").Range("M1:N1")
Set sakiRng13 = Workbooks(データ1.xls).Worksheets("データ1").Range("R1:S1")

ここで、連続した列を抽出する際はA1:B1:・・・とできるが、抽出したくない列が途中に来ると、次に来る列でまたセットしなければできないことが判明。
(1行で書けた人は教えてください。)

抽出条件をクエリを使って設定。B1:B2としたが、B1に題名として「サービス」、B2に値として「返却」と書いて、抽出元のデータ.xlsの1行目(題名)がサービスとなっている列について「返却」と為っている行を抽出するように設定した。

抽出範囲としてはデータ.xlsのA8:N500を設定。A8(8行目)に題名が来るように設定しなければエラーとなる。

そして、AdvancedFilterでコピー。
.AdvancedFilter xlFilterCopy, myQry1, CopyToRange:=sakiRng11

xlFilterCopyで他シート(他ファイル)へコピー。myQry1に抽出条件をセット。
CopyToRange:=sakiRng11でコピー先の同じ題名(1行目の値)の物についてコピーを行う。また、列を指定しているので、指定した物だけコピーできる。指定した列について、題名を並び替えておけば、題名の列がそのままコピーされるので、並び替えができる。

エラーが出まくって大変苦労した。

AdvancedFilterを使用するときは以下の点に注意しよう。

・コピー元のレンジの範囲は必ず題名が頭に来るようにしよう。
・コピー先の1行目にはコピー元の題名と一致させよう。
・コピー先のコピーする列の指定は1個1個書く必要あり(A1:C1の指定ではコピーできない)。

以上で、必要な列を並び替えて抽出し、他ファイルへコピーできた。

次は、VLOOKUP関数だ。

VBAではなくても使いにくいので、がんばって使えるようにしよう!!

忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP