|
Sub シート振り分け()
Dim Sh As Worksheet, Psh As Worksheet
Dim Snm As String
Dim MyR As Range, C As Range
Set Sh = Worksheets("Sheet1")
Application.ScreenUpdating = False
Sh.Range("A1").CurrentRegion.Sort Key1:=Sh.Columns(12), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlSortColumns
Sh.Range("A1").Subtotal 12, xlCount, Array(2)
Set MyR = Range("A1", Range("A65536").End(xlUp)) _
.SpecialCells(2)
For Each C In MyR.Areas
Snm = CStr(C.Range("L1").Value)
On Error Resume Next
Set Psh = Worksheets(Snm)
If Err.Number <> 0 Then
Set Psh = Worksheets.Add(Before:=Worksheets(1))
Psh.Name = Snm: Err.Clear
End If
On Error GoTo 0
C.EntireRow.Copy
Psh.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False: Set Psh = Nothing
Next
Sh.Activate: Sh.Cells.RemoveSubtotal
Application.ScreenUpdating = True
Set MyR = Nothing: Set Sh = Nothing
End Sub
|
|