トップ > VBA・VBS > Excel VBA > 条件でデータを別シートに振り分ける
Excel VBA(担当者別・月別のシートを自動で作る)
「担当者ごとにシートを分けて渡して」と言われると、フィルターして、コピーして、新しいシートに貼って、名前を付けて…を担当者の人数分。人数が多いほど心が折れる作業です。このレシピでは、指定した列の値ごとに行を振り分け、値の名前のシートを自動で作って配るマクロを作ります。再実行すれば各シートは最新状態に作り直されるので、データが差し替わっても振り分け直しは1回で済みます。
✅ これができます:ボタン1つで、データを担当者別・月別などのシートに振り分けられるようになります


実行前(左)は担当者が混ざった1枚のデータ。実行すると(右のように)担当者名のシートが自動で作られ、行が振り分けられます
Option Explicit ' ===== 設定エリア:変えるのはここだけ ===== ' ① 振り分けもとになるデータのシート名 Const DATA_SHEET As String = "データ" ' ② 振り分けのキーになる列の番号(A列=1、B列=2…。例は担当者の列) Const KEY_COL As Long = 2 ' ③ データの見出し行の数 Const HEADER_ROWS As Long = 1 ' ===== ここから下は変えなくてOK ===== Sub 担当者別に振り分ける() Dim wsData As Worksheet, wsOut As Worksheet Dim dicKeys As Object, dicRow As Object Dim lastRow As Long, lastCol As Long, r As Long Dim key As String, sheetName As String Dim k As Variant Dim skipSelf As Long, msg As String Set dicKeys = CreateObject("Scripting.Dictionary") Set dicRow = CreateObject("Scripting.Dictionary") Set wsData = ThisWorkbook.Worksheets(DATA_SHEET) lastRow = wsData.Cells(wsData.Rows.Count, KEY_COL).End(xlUp).Row lastCol = wsData.Cells(HEADER_ROWS, wsData.Columns.Count).End(xlToLeft).Column ' まず、振り分け先の値の顔ぶれを集める(データシートと同じ名前の値は、安全のため対象外) skipSelf = 0 For r = HEADER_ROWS + 1 To lastRow key = Trim(CStr(wsData.Cells(r, KEY_COL).Value)) If key <> "" Then If シート名に使える形に(key) = DATA_SHEET Then skipSelf = skipSelf + 1 ElseIf Not dicKeys.Exists(key) Then dicKeys.Add key, True End If End If Next r If dicKeys.Count = 0 Then MsgBox "振り分けるデータがありませんでした。", vbExclamation, "振り分け" Exit Sub End If ' 値ごとにシートを用意し、見出しを写して、書き出し位置を初期化する Application.ScreenUpdating = False For Each k In dicKeys.Keys sheetName = シート名に使える形に(CStr(k)) If Not dicRow.Exists(sheetName) Then Set wsOut = シートを用意する(sheetName) wsOut.Cells.Clear wsData.Range(wsData.Cells(1, 1), wsData.Cells(HEADER_ROWS, lastCol)).Copy wsOut.Cells(1, 1) dicRow.Add sheetName, HEADER_ROWS + 1 End If Next k ' データを1行ずつ、行き先のシートへ写す For r = HEADER_ROWS + 1 To lastRow key = Trim(CStr(wsData.Cells(r, KEY_COL).Value)) If key <> "" Then sheetName = シート名に使える形に(key) Set wsOut = ThisWorkbook.Worksheets(sheetName) wsOut.Cells(dicRow(sheetName), 1).Resize(1, lastCol).Value = wsData.Cells(r, 1).Resize(1, lastCol).Value dicRow(sheetName) = dicRow(sheetName) + 1 End If Next r Application.ScreenUpdating = True wsData.Activate msg = dicKeys.Count & " 件のシートに振り分けました。" If skipSelf > 0 Then msg = msg & vbCrLf & "(データシートと同じ名前の値 " & skipSelf & " 行は、安全のため飛ばしました)" End If MsgBox msg, vbInformation, "振り分け" End Sub Private Function シート名に使える形に(ByVal s As String) As String Dim bad As Variant, b As Variant bad = Array("\", "/", "?", "*", "[", "]", ":") For Each b In bad s = Replace(s, CStr(b), "_") Next b If Len(s) > 31 Then s = Left(s, 31) If s = "" Then s = "無題" シート名に使える形に = s End Function Private Function シートを用意する(ByVal sheetName As String) As Worksheet Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name = sheetName Then Set シートを用意する = ws Exit Function End If Next ws Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)) ws.Name = sheetName Set シートを用意する = ws End Function
コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

| 番号 | 変える場所 | 意味 | 書き方・例 |
|---|---|---|---|
| 1 | DATA_SHEET | 振り分けもとになるデータのシート名 | 自分のブックのデータシートの名前に書き換えます |
| 2 | KEY_COL | 振り分けのキーになる列の番号 | 担当者が B列なら 2、月が A列なら 1。この列の値が、そのままシート名になります |
| 3 | HEADER_ROWS | データの見出し行の数 | 見出しが1行なら 1。見出しは各シートの先頭にも写されます |
⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。
⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。
⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。
⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。
⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。
実行のたびに、振り分け先の各シートはまっさらな状態から作り直されます(データシート自身は変更されません)。担当者が増えたら、その人のシートも自動で増えます。なお、以前はあった担当者がデータから消えた場合、その人の古いシートは残ったままになるので、不要なら手で消してください。
②を「月」の列に変えるだけです。日付の列しか無い場合は、データ側に「月」を取り出す列(=TEXT関数や MONTH関数)を1列足してから、その列をキーにします。
振り分け後にシートごとに保存する処理を続けると、「担当者別ファイル」の配布まで一気に行けます。「シートごとに別ブックとして分割保存」のレシピと組み合わせる形です。
「佐藤」と「佐藤 」のような表記ゆれは別のシートになります。前後の空白は自動で取り除きますが、全角・半角や敬称の有無は元データ側で揃えるのが確実です。
次はこれ:全シートをPDFで一括出力する
振り分けたシートは、PDFで一括出力して配布するところまで自動化できます
A. 見出し行は書式ごと写しますが、データ行は値だけを写します。色分けなどの書式が重要な表では、実行後に必要な書式を足してください。
A. 1行ずつ写す作りなので、数千行なら実用範囲です。数万行になると時間がかかる場合があります。
A. データに最初に現れた順に作られます。名前順にしたい場合は、実行後にシート見出しを並べ替えてください。