|
Option Explicit
Sub 구분별_시트분리()
Const AFno% = 4
Dim SD As Object
Dim TSht As Variant
Dim vR As Variant
Dim i As Long
Set SD = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Application.Calculation = xlManual
With Worksheets("Sheet1").Range("A1").CurrentRegion
If .Parent.AutoFilterMode Then .AutoFilter
vR = .Columns(AFno).Value
For i = 2 To UBound(vR, 1)
TSht = vR(i, 1)
If TSht <> "" Then
If Not SD.Exists(TSht) Then
SD.Add TSht, Nothing
.AutoFilter AFno, TSht, , , False
If IsError(Evaluate("'" & TSht & "'!A1")) Then
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = TSht
Else
Sheets(CStr(TSht)).UsedRange.Clear
End If
.Copy Sheets(CStr(TSht)).Range("A1")
End If
End If
Next i
.Parent.Activate
If .Parent.AutoFilterMode Then .AutoFilter
End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
MsgBox " 작업완료...!", 64, " ♣ 구분별_시트분리 ♣"
End Sub |
|