Hi pottie8
Welcome to the forum...
A few questions...
You did not mention a scenario if sheet already exists...
You want Columns O & P to be formatted as number? ... They are tel numbers so perhaps TEXT...
Will amend once you advise...Here is one way...
Option Explicit
Sub pottie8()
Dim Val, i As Long
Application.ScreenUpdating = False
With Sheet1.Cells(1).CurrentRegion
Range(.Cells(2, 10), .Cells(.Count, 10)).AdvancedFilter xlFilterCopy, , .Range("V1"), True
With .Range("V1").CurrentRegion: .Sort key1:=Range("V1"), order1:=xlAscending, Header:=xlNo: Val = .Value: .Clear: End With
For i = 2 To UBound(Val)
.AutoFilter 10, Val(i, 1)
.SpecialCells(12).EntireRow.Copy
Sheets.Add(, Sheets(Sheets.Count)).Name = Val(i, 1)
Range("A1").PasteSpecial xlPasteAll
Columns.AutoFit
.AutoFilter
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Bookmarks