Split sheet into multiple protected workbooks and save?

[sneeky]

Hi. As an intermediate VBA writer - if someone could please help me with and potentially give comments on the code.
I have a sheet, with 70,000+ employees.
The main manager's name for several thousand is in column AF and I want every unique name/value to have a seperate workbook created, with the book named by the managers name.
The last column is AP.
Their are two top rows, with row 2 having headers.
The sheet is also protected with formatted which also needs to be copied over completely.
I.e. I need to send the same sheet to multiple managers, for only their employees?
Pls help...​

 

[JimSnyder]

Which part are you having trouble with? Each piece should be pretty easy.

 

[sneeky]

This is my code. No matter what I do. It will only cause separate sheets and not a separate workbooks with a single manager.
At best, i will get a multiple workbooks with all the managers on as separate sheets which I don't want either. It needs to run so it loops for each manager then removes the last sheet or something, and start again.. I'm not sure how?

Sub SplitData()
'
ActiveSheet.Protect Password:="aaaaa", UserInterfaceOnly:=True


    Const NameCol = "AD"
    Const TopRow = 2
    Const HeaderRow = 3
    Const FirstRow = 4
    Dim i As Long
    Dim n As Long
    Dim SrcBook As Workbook
    Dim TrgBook As Workbook
    Dim SrcSheet As Worksheet
    Dim TrgSheet As Worksheet
    Dim SrcRow As Variant
    Dim LastRow As Variant
    Dim TrgRow As Variant
    Dim Student As Variant
    Application.ScreenUpdating = False
    n = Worksheets.Count
    Set SrcSheet = ActiveSheet
    LastRow = SrcSheet.Cells(SrcSheet.Rows.Count, NameCol).End(xlUp).Row
    For SrcRow = FirstRow To LastRow
        manager = SrcSheet.Cells(SrcRow, NameCol).Value
        Set TrgSheet = Nothing
        On Error Resume Next
        Set TrgSheet = Worksheets(manager)
        On Error GoTo 0
        If TrgSheet Is Nothing Then
            SrcSheet.Copy After:=Worksheets(Worksheets.Count)
            Set TrgSheet = Worksheets(Worksheets.Count)
            TrgSheet.Cells.ClearContents
            TrgSheet.Name = manager
            SrcSheet.Rows(TopRow).Copy Destination:=TrgSheet.Rows(TopRow)
            SrcSheet.Rows(HeaderRow).Copy Destination:=TrgSheet.Rows(HeaderRow)
           
        End If
        TrgRow = TrgSheet.Cells(TrgSheet.Rows.Count, NameCol).End(xlUp).Row + 1
        SrcSheet.Rows(SrcRow).Copy Destination:=TrgSheet.Rows(TrgRow)
        
    Next SrcRow
    
    Application.DisplayAlerts = False
    For i = Worksheets.Count To n + 1 Step -1
        Set TrgSheet = Worksheets(i)
        TrgSheet.EnableOutlining = True
        TrgSheet.Protect Password:="aaaaa", UserInterfaceOnly:=True
        TrgSheet.SaveAs Filename:=TrgSheet.Name & ".xls", FileFormat:=xlOpenXMLWorkbook
'        TrgSheet.Delete
    
    Next i
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    
End Sub