このようなデータを都道県別にシートを分けたい時のマクロ
↓
'---------------------------------
Sub セルの値別にシートを分ける()
'---------------------------------
Dim i%
Application.ScreenUpdating = False
With Worksheets(1)
For i = 2 To .Range("A60000").End(xlUp).Row
Call AddLine(i, .Cells(i, 1).value)
Next
End With
Application.ScreenUpdating = True
End Sub
'---------------------------------
Sub AddLine(lineNum%, sheetName$)
'---------------------------------
Dim lastLine%
Call checkAndMake(sheetName)
lastLine = Worksheets(sheetName).Range("A60000").End(xlUp).Row + 1
Worksheets(1).Rows(lineNum).Copy
Worksheets(sheetName).Rows(lastLine).Insert Shift:=xlDown
End Sub
'---------------------------------
Sub checkAndMake(sheetName$)
'---------------------------------
Dim tmpWS As Worksheet
On Error Resume Next
Set tmpWS = Worksheets(sheetName)
If tmpWS Is Nothing Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = sheetName
Worksheets(1).Rows(1).Copy
Worksheets(sheetName).Rows(1).Insert Shift:=xlDown
End If
On Error GoTo 0
End Sub
上記はA列でシート分けしましたが、
違う列でシート分けしたい場合は
便利。
↓も便利
