Excel VBA データから指定項目のデータを各シートに振り分ける

スポンサーリンク
スポンサーリンク

データから指定項目のデータを各シートに振り分ける方法

元のデータから指定した項目列の値を基準として
その値のデータを別シートに転記する方法をご紹介します。

例として
下記の表の2列目の地区でデータをまとめて
地区名のシートを作成して
そのシートに地区ごとのデータを
元データから転記するコードを説明します。

実行イメージ

実行前

実行後


コードの解説

Sub 転記()
Dim データ範囲 As Range
Dim 条件範囲 As Range
Dim 列 As Long
Dim シート As Worksheet
Dim i As Long
Set データ範囲 = Range("A1").CurrentRegion
    列 = 2
Set 条件範囲 = データ範囲.Cells(1).Offset(0, データ範囲.Columns.Count + 1)
    データ範囲.Columns(列).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=条件範囲, Unique:=True
Set 条件範囲 = 条件範囲.CurrentRegion
For i = 2 To 条件範囲.Rows.Count
    条件範囲.Cells(2, 1).Value = 条件範囲.Cells(i, 1).Value
Set シート = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    シート.Name = 条件範囲.Cells(2, 1).Value
    データ範囲.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=条件範囲.Rows("1:2"), CopyToRange:=シート.Range("A1")
    シート.Range("A1").CurrentRegion.EntireColumn.AutoFit
Next i
    条件範囲.Clear
End Sub

コードの解説

2行目
【Dim データ範囲 As Range】

データの範囲を格納する
変数「データ範囲」を
オブジェクト型(Range)で宣言します。


3行目
【Dim 条件範囲 As Range】

データの抽出条件があるセルを格納する
変数「条件範囲」を
オブジェクト型(Range)で宣言します。


4行目
【Dim 列 As Long】

抽出条件となる(転記するデータの基準となる)
項目の列番号を格納する
変数「列」を
長整数型(Long)で宣言します。


5行目
【Dim シート As Worksheet】

データの転記先となる
追加されるワークシートを格納する
変数「シート」を
オブジェクト型(Worksheet)で宣言します。


6行目
【Dim i As Long】

データの抽出条件があるセル範囲が
格納されている条件範囲の行番号を格納し
繰り返し処理で使用する
カウンター変数「i」を
長整数型(Long)で宣言します。


7行目
【Set データ範囲 = Range(“A1”).CurrentRegion】

A1セルから始まるデータ範囲を
Range【レンジ】オブジェクトの
CurrentRegion【カレントリージョン】プロパティを使用して
データ範囲を参照して
オブジェクト変数の「データ範囲」に
Setキワードを使用して格納します。


8行目
【列 = 2】

転記するデータの基準となる項目の列番号
ここでは2列目の項目「地区」の列番号「2」を
変数「列」に代入します。


9行目
【Set 条件範囲 = データ範囲.Cells(1).Offset(0, データ範囲.Columns.Count + 1)】

データ範囲をCells【セルズ】プロパティで引数を1
つまりここではA1セルを参照して
参照したA1セルからOffset【オフセット】プロパティで行方向の移動は0
列方向はColumns【カラムズ】プロパティで列を参照し
Count【カウント】プロパティで列数を取得してその列数に1を加算した列
つまり、ここでは「データ範囲.Cells(1).Offset(0,5)」になり
A1セルから数えて5列目を参照することになり
データ範囲の最終列D列の2列先のF1セルが参照されたことになります。
そのセルをオブジェクト変数の「条件範囲」に
setキーワードを使用して代入します。


10行目~11行目
【データ範囲.Columns(列).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=条件範囲, Unique:=True】

データ範囲の列をColumns【カラムズ】プロパティで参照します。
引数に変数「列」を設定して
ここではデータ範囲の2列目の地区の列を参照します。
参照した列に
フィルターオプションの設定を表す
AdvancedFilter【アドバンスドフィルター】メソッドを実行します。
データの抽出先を表す
引数Action【アクション】には引数CopyToRange【コピートゥレンジ】で
指定したセル範囲にデータをコピーして抽出するを表す
xlFiltercopyを設定し、その抽出先を表す
引数CopyToRange【コピートゥレンジ】には
オブジェクト変数「条件範囲」を指定します。
重複しているデータを抽出するかどうかを設定する
引数Unique【ユニーク】にTrueを設定して
重複している値は抽出しない設定にします。
つまり、2列目の地区のデータの重複する値をのぞいて
データをコピーして
オブジェクト変数「条件範囲」に格納されている
F1セルを基準にして地区列のユニーク(重複のない)なデータを
貼り付けています。


12行目
【Set 条件範囲 = 条件範囲.CurrentRegion】

AdvancedFilter【アドバンスドフィルター】メソッドで抽出したデータ範囲を
F1セルが格納されている
オブジェクト変数「条件範囲」の
データ範囲を参照する
CurrentRegion【カレントリージョン】プロパティを使用して
F1セルから始まるデータ範囲を取得して
オブジェクト変数「条件範囲」に
setキーワードを使用して代入します。


13行目
【For i = 2 To 条件範囲.Rows.Count】

For Next【フォアネクスト】ステートメントを使用して
繰り返し処理の始まりです。
F1セルから始まるデータ範囲が格納されている
オブジェクト変数「条件範囲」
つまり、AdvancedFilter【アドバンスドフィルター】メソッドで抽出した
地区のユニーク(重複の無い)なデータ範囲を
行を表すRows【ロウズ】プロパティで参照して
Count【カウント】プロパティでそのデータ範囲の行数を取得します。
そして2~データ範囲の行数を繰り返し処理の中で
カウンター変数「i」に順次代入します。


14行目
【条件範囲.Cells(2, 1).Value = 条件範囲.Cells(i, 1).Value】

オブジェクト変数「条件範囲」に格納されている
1列目のカウンター変数「i」行目のセルの値を
オブジェクト変数「条件範囲」の
1列目の2行目に繰り返し処理の中で順次代入します。


15行目
【Set シート = Worksheets.Add(after:=Worksheets(Worksheets.Count))】

Worksheets【ワークシーツ】コレクションの
Add【アド】メソッドを使用して新しいワークシートを追加して
オブジェクト変数「シート」に
Setキーワードを使用して代入します。


16行目
【シート.Name = 条件範囲.Cells(2, 1).Value】

追加した新しいシートが格納されている
オブジェクト変数「シート」のName【ネーム】プロパティに
オブジェクト変数「条件範囲」の1列目の2行目のセルの値を設定します。
つまり、F2セルに設定されている地区の名前を新しいシート名として
設定します。


17行目~18行目
【データ範囲.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=条件範囲.Rows(“1:2”), CopyToRange:=シート.Range(“A1”)】

データ範囲に
フィルターオプションの設定を表す
AdvancedFilter【アドバンスドフィルター】メソッドを実行します。
データの抽出先を表す
引数Action【アクション】には引数CopyToRange【コピートゥレンジ】で
指定したセル範囲にデータをコピーして抽出するを表す
xlFiltercopyを設定し、その抽出先を表す
引数CopyToRange【コピートゥレンジ】には
新しく追加したワークシートを表す
オブジェクト変数「シート」のA1セルを指定します。
ワークシート上に作成した抽出条件を表す
引数CriteriaRange【クライテリアレンジ】には
オブジェクト変数「条件範囲」の
Rows【ロウズ】プロパティで1行目と2行目を指定します。

条件範囲の1~2行目の値の条件でデータ範囲から
AdvancedFilter【アドバンスドフィルター】メソッドでデータを抽出し
新しく追加したシートのA1セルに抽出したデータを貼り付けます。


19行目
【シート.Range(“A1”).CurrentRegion.EntireColumn.AutoFit 】
オブジェクト変数「シート」に格納されているワークシートの
A1セルを基準にCurrentRegion【カレントリージョン】プロパティで
データ範囲を参照してそのすべての列を表す
EntireColumn【エンターカラム】プロパティで全体列を参照し
AutoFit【オートフィット】メソッドを使用して列幅を調整します。


20行目
【Next i】

13行目~ここまでの処理を
オブジェクト変数「条件範囲」の行数分繰り返します。


21行目
【条件範囲.Clear】

繰り返し処理を抜けたあと
データ範囲に作成した
オブジェクト変数「条件範囲」に格納された
セル範囲をClear【クリア】メソッドを使用して削除します。


以上で
データから指定項目のデータを各シートに振り分ける
方法についての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク