Here's the code. Run this from within the source workbook
Code:
Sub SplitWorkbook()
Dim colLetter As String, SavePath As String
Dim lastValue As String
Dim wb As Workbook
Dim lng As Long
Dim currentRow As Long
colLetter = "P"
SavePath = "" 'Indicate the path to save
If SavePath = "" Then SavePath = ThisWorkbook.Path
'Sort the workbook.
With ThisWorkbook.Worksheets(1)
.Sort.SortFields.Add Key:=.Range(colLetter & ":" & colLetter), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Cells
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For lng = 2 To .Range(colLetter & .Rows.Count).End(xlUp).Row
If .Cells(lng, colLetter).Value = "" Then Exit For
lastValue = .Cells(lng, colLetter).Value
.Cells.AutoFilter field:=.Cells(lng, colLetter).Column, Criteria1:=lastValue
lng = .Cells(.Rows.Count, colLetter).End(xlUp).Row
Set wb = Application.Workbooks.Add(xlWorksheet)
.Rows(1 & ":" & lng).Copy wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp)
wb.SaveAs SavePath & "\" & lastValue, 50
wb.Close
Next
.AutoFilterMode = False
End With
End Sub
Bookmarks