Employee Roster Separation

[Pestomania]

Hi there,

I am trying to set up the macros that save off an excel sheet based on Mgr1 Name.

My thoughts:

1. A Macro that moves all employees that report to a specific Mgr to a new excel sheet.
2. Rename the sheet to that Mgr name.
3. Format it to look nice (I can do the formatting code)
4. Print to PDF
5. Save the PDF with the Mgr Name in a certain file.

BCM Copy of Organziational Structure (for upload).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Name	Org Code	user.mobile	U.S. Person	Company	Department	Site	ID	organizationalPerson.homePhone	Email	Mgr1 Name	Mgr1 ID	Mgr1 Email	Mgr2 Name	Mgr2 ID	Mgr2 Email	Mgr3 Name	Mgr3 ID	Mgr3 Email	Mgr4 Name	Mgr4 ID	Mgr4 Email	Mgr5 Name	Mgr 5 ID	Mgr5 Email	Mgr6 Name	Mgr6 ID	Mgr6 Email	Mgr7 Name	Mgr7 ID	Mgr7 Email	Mgr8 Name	Mgr8 ID	Mgr8 Email	Mgr9	organizationalPerson.mobile	organizationalPerson.physicalDeliveryOfficeName
2	Employee 1										Manager 5			Manager 4
3	Employee 2										Manager 4			Manager 7
4	Employee 3										Manager 15			Manager 6
5	Employee 4										Manager 6			Manager 2
6	Employee 5										Manager 6			Manager 1
7	Employee 6										Manager 15			Manager 4
8	Employee 7										Manager 12			Manager 2
9	Employee 8										Manager 2			Manager 8
10	Employee 9										Manager 7			Manager 2
11	Employee 10										Manager 7			Manager 14
12	Employee 11										Manager 4			Manager 8
13	Employee 12										Manager 5			Manager 2
14	Employee 13										Manager 14			Manager 13
15	Employee 14										Manager 6			Manager 3
16	Employee 15										Manager 15			Manager 13
17	Employee 16										Manager 13			Manager 9
18	Employee 17										Manager 15			Manager 9
19	Employee 18										Manager 12			Manager 13
20	Employee 19										Manager 15			Manager 6
21	Employee 20										Manager 1			Manager 3
22	Employee 21										Manager 1			Manager 6
23	Employee 22										Manager 12			Manager 10
24	Employee 23										Manager 8			Manager 11
25	Employee 24										Manager 2			Manager 9
26	Employee 25										Manager 13			Manager 13
27	Employee 26										Manager 13			Manager 1
28	Employee 27										Manager 6			Manager 6
29	Employee 28										Manager 9			Manager 6
30	Employee 29										Manager 2			Manager 2
31	Employee 30										Manager 2			Manager 4
32	Employee 31										Manager 11			Manager 1
33	Employee 32										Manager 10			Manager 11
34	Employee 33										Manager 15			Manager 5
35	Employee 34										Manager 8			Manager 11
36	Employee 35										Manager 14			Manager 11
37	Employee 36										Manager 11			Manager 3
38	Employee 37										Manager 6			Manager 15
39	Employee 38										Manager 15			Manager 4
40	Employee 39										Manager 3			Manager 10
41	Employee 40										Manager 5			Manager 7
42	Employee 41										Manager 4			Manager 9
43	Employee 42										Manager 15			Manager 6
44	Employee 43										Manager 8			Manager 5
45	Employee 44										Manager 13			Manager 14
46	Employee 45										Manager 14			Manager 14
47	Employee 46										Manager 7			Manager 7
48	Employee 47										Manager 10			Manager 11
49	Employee 48										Manager 13			Manager 10
50	Employee 49										Manager 7			Manager 12
51	Employee 50										Manager 3			Manager 5
52	Employee 51										Manager 9			Manager 2
53	Employee 52										Manager 10			Manager 12
54	Employee 53										Manager 8			Manager 9
55	Employee 54										Manager 4			Manager 4
56	Employee 55										Manager 12			Manager 7
57	Employee 56										Manager 2			Manager 13
58	Employee 57										Manager 10			Manager 4
59	Employee 58										Manager 8			Manager 5
60	Employee 59										Manager 4			Manager 8
61	Employee 60										Manager 9			Manager 3
62	Employee 61										Manager 4			Manager 2
63	Employee 62										Manager 14			Manager 9
64	Employee 63										Manager 7			Manager 5
user

 

[sxhall]

Hi Pestomania,

A couple of questions if I may...

1. Is it just the list of employee names that you need from the above example?
2. You say 'move' so they will no longer be in the original list? Or do you need them just 'copied' across to the PDF?
3. Also how would you like to have people select the managers name?
   • Dropdown selection on a sheet - trigger macro with a button click
   • Custom userform - in which case are you able to create a userform?

I'm happy to help as I've done a similar exercise to this before where I work.

 

[Pestomania]

Hi Pestomania,

A couple of questions if I may...

1. Is it just the list of employee names that you need from the above example?
2. You say 'move' so they will no longer be in the original list? Or do you need them just 'copied' across to the PDF?
3. Also how would you like to have people select the managers name?
   • Dropdown selection on a sheet - trigger macro with a button click
   • Custom userform - in which case are you able to create a userform?

I'm happy to help as I've done a similar exercise to this before where I work.

Hi @sxhall, I would "copy" them so that I don't have to regularly orginial file.

I want to save each of the managers lists off as a PDF with their name and the managers can find their own documents. I may consider doing an email in time, which I think I can do myself.

Here was my thought on process, but I am not sophisticated enough:

1. Copy the entire row over to a new excel sheet
2. Name the document after the Manager
3. Reformat it (I'll do this). I only need the first 12 columns for the pdf, but I'll automate it to hide everything else.
4. Save the file as a PDF with the managers name.
5. Once created, I may have it email out to the manager.

 

[sxhall]

OK,

Will add the manager selection as a dropdown box which will need to be on a seperate sheet, will need to activate the macro via a button click.

 

[Sequoyah]

Hello everyone,
here is my attempt, I'll briefly explain how it works. Remember to adapt the sheet name.

1. A collection of all values in column K is created in memory.
2. A loop is executed, filtering the sheet by individual values (manager's name).
3. The save path (same as the Excel file, customizable if necessary) and the PDF file name are assigned.
4. A filter is applied to column K, and for each unique value, only the visible cells are copied.
5. A sheet with the manager's name is added, the data is pasted, and it is then exported as a PDF (adjusting the content to fit on one page).
6. At the end, the filter is removed.

In your request, you mentioned manual operations to be performed afterward, but if you use VBA to automate the procedures, you can handle everything automatically. If the sheet containing the data has proper formatting, it will be retained when copying the data to a new sheet (choosing the columns to copy). If the goal is to create a PDF to send via email, you likely don't need a separate sheet; you can use the same macro to create the PDF and send it via Outlook.

Sub Pestomania()

'https://www.mrexcel.com/board/threads/employee-roster-separation.1265153/

    Dim SourceWs         As Worksheet
    Dim cel         As Range, myRng As Range
    Dim Itm         As Variant
    Dim LastRow     As Long
    Dim collMgr   As New Collection
    Dim MyFile As String
    
    Set SourceWs = ThisWorkbook.Sheets("Sheet1") '<<============ ADAPT Sheet name
        
    On Error Resume Next
    '1)
    For Each cel In SourceWs.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        collMgr.Add cel.Value, CStr(cel.Value)
    Next
    On Error GoTo 0
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    '2)
    For Each Itm In collMgr
    
    '3)
    MyFile = ThisWorkbook.Path & Application.PathSeparator & Itm & ".PDF"
    
    '4)
        
    SourceWs.Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=11, Criteria1:=Itm
    
    LastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row
    
    SourceWs.Range("A1:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    
    '5)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Itm
        With ActiveSheet
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            With ActiveSheet.PageSetup
                .Zoom = False
                .Orientation = xlPortrait
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=MyFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
        
    End With
    
    Next
    '6)
    On Error Resume Next
    SourceWs.ShowAllData
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

 

[Pestomania]

Hello everyone,
here is my attempt, I'll briefly explain how it works. Remember to adapt the sheet name.

1. A collection of all values in column K is created in memory.
2. A loop is executed, filtering the sheet by individual values (manager's name).
3. The save path (same as the Excel file, customizable if necessary) and the PDF file name are assigned.
4. A filter is applied to column K, and for each unique value, only the visible cells are copied.
5. A sheet with the manager's name is added, the data is pasted, and it is then exported as a PDF (adjusting the content to fit on one page).
6. At the end, the filter is removed.

In your request, you mentioned manual operations to be performed afterward, but if you use VBA to automate the procedures, you can handle everything automatically. If the sheet containing the data has proper formatting, it will be retained when copying the data to a new sheet (choosing the columns to copy). If the goal is to create a PDF to send via email, you likely don't need a separate sheet; you can use the same macro to create the PDF and send it via Outlook.

Sub Pestomania()

'https://www.mrexcel.com/board/threads/employee-roster-separation.1265153/

    Dim SourceWs         As Worksheet
    Dim cel         As Range, myRng As Range
    Dim Itm         As Variant
    Dim LastRow     As Long
    Dim collMgr   As New Collection
    Dim MyFile As String
   
    Set SourceWs = ThisWorkbook.Sheets("Sheet1") '<<============ ADAPT Sheet name
       
    On Error Resume Next
    '1)
    For Each cel In SourceWs.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        collMgr.Add cel.Value, CStr(cel.Value)
    Next
    On Error GoTo 0
   
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
   
    '2)
    For Each Itm In collMgr
   
    '3)
    MyFile = ThisWorkbook.Path & Application.PathSeparator & Itm & ".PDF"
   
    '4)
       
    SourceWs.Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=11, Criteria1:=Itm
   
    LastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row
   
    SourceWs.Range("A1:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy
   
    '5)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Itm
        With ActiveSheet
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            With ActiveSheet.PageSetup
                .Zoom = False
                .Orientation = xlPortrait
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=MyFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
       
    End With
   
    Next
    '6)
    On Error Resume Next
    SourceWs.ShowAllData
    On Error GoTo 0
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   
End Sub

Hello everyone,
here is my attempt, I'll briefly explain how it works. Remember to adapt the sheet name.

1. A collection of all values in column K is created in memory.
2. A loop is executed, filtering the sheet by individual values (manager's name).
3. The save path (same as the Excel file, customizable if necessary) and the PDF file name are assigned.
4. A filter is applied to column K, and for each unique value, only the visible cells are copied.
5. A sheet with the manager's name is added, the data is pasted, and it is then exported as a PDF (adjusting the content to fit on one page).
6. At the end, the filter is removed.

In your request, you mentioned manual operations to be performed afterward, but if you use VBA to automate the procedures, you can handle everything automatically. If the sheet containing the data has proper formatting, it will be retained when copying the data to a new sheet (choosing the columns to copy). If the goal is to create a PDF to send via email, you likely don't need a separate sheet; you can use the same macro to create the PDF and send it via Outlook.

Sub Pestomania()

'https://www.mrexcel.com/board/threads/employee-roster-separation.1265153/

    Dim SourceWs         As Worksheet
    Dim cel         As Range, myRng As Range
    Dim Itm         As Variant
    Dim LastRow     As Long
    Dim collMgr   As New Collection
    Dim MyFile As String
   
    Set SourceWs = ThisWorkbook.Sheets("Sheet1") '<<============ ADAPT Sheet name
       
    On Error Resume Next
    '1)
    For Each cel In SourceWs.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        collMgr.Add cel.Value, CStr(cel.Value)
    Next
    On Error GoTo 0
   
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
   
    '2)
    For Each Itm In collMgr
   
    '3)
    MyFile = ThisWorkbook.Path & Application.PathSeparator & Itm & ".PDF"
   
    '4)
       
    SourceWs.Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=11, Criteria1:=Itm
   
    LastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row
   
    SourceWs.Range("A1:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy
   
    '5)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Itm
        With ActiveSheet
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            With ActiveSheet.PageSetup
                .Zoom = False
                .Orientation = xlPortrait
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=MyFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
       
    End With
   
    Next
    '6)
    On Error Resume Next
    SourceWs.ShowAllData
    On Error GoTo 0
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   
End Sub

This did the trick!

It wasn't so much that I would do manual work as I would add in the code to do the formatting (I am working on that now). After the full dataset was complete, it was over 650 different managers so it took almost an hour to complete the full parsing, but oh well.

Thank you very much!

 

[Pestomania]

@Sequoyah

Is there a way to remove any invalid characters for file names and anythign that has any text between parantheses mark.

I have a lot of names with "(i)" and "(EXT)" at the end of their name, I just want to remove that text completely.

And can there be a "popup" that uses "Unique Values of Column G" and allows you to select what sites you want to process under? So if I only want one of 20 sites, it will only search for those that meet the criteria of that one site?

Thank you

Sub Pestomania()

'https://www.mrexcel.com/board/threads/employee-roster-separation.1265153/

    Dim SourceWs         As Worksheet
    Dim cel         As Range, myRng As Range
    Dim Itm         As Variant
    Dim LastRow     As Long
    Dim collMgr   As New Collection
    Dim MyFile As String
    
    Set SourceWs = ThisWorkbook.Sheets("Employee_Roster") '<<============ ADAPT Sheet name
        
    On Error Resume Next
    '1)
    For Each cel In SourceWs.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        collMgr.Add cel.Value, CStr(cel.Value)
    Next
    On Error GoTo 0
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    '2)
    For Each Itm In collMgr
    
    '3)
    MyFile = ThisWorkbook.Path & Application.PathSeparator & Itm & ".PDF"
    
    '4)
        
    SourceWs.Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=11, Criteria1:=Itm
    
    LastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row
    
    SourceWs.Range("A1:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    
    '5)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Itm
        With ActiveSheet
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            With ActiveSheet.PageSetup
                .Zoom = False
                .Orientation = xlPortrait
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=MyFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
        
    End With
    
    Next
    '6)
    On Error Resume Next
    SourceWs.ShowAllData
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

 

[Pestomania]

@Sequoyah

Is there a way to remove any invalid characters for file names and anythign that has any text between parantheses mark.

I have a lot of names with "(i)" and "(EXT)" at the end of their name, I just want to remove that text completely.

And can there be a "popup" that uses "Unique Values of Column G" and allows you to select what sites you want to process under? So if I only want one of 20 sites, it will only search for those that meet the criteria of that one site?

Thank you

Sub Pestomania()

'https://www.mrexcel.com/board/threads/employee-roster-separation.1265153/

    Dim SourceWs         As Worksheet
    Dim cel         As Range, myRng As Range
    Dim Itm         As Variant
    Dim LastRow     As Long
    Dim collMgr   As New Collection
    Dim MyFile As String
   
    Set SourceWs = ThisWorkbook.Sheets("Employee_Roster") '<<============ ADAPT Sheet name
       
    On Error Resume Next
    '1)
    For Each cel In SourceWs.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        collMgr.Add cel.Value, CStr(cel.Value)
    Next
    On Error GoTo 0
   
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
   
    '2)
    For Each Itm In collMgr
   
    '3)
    MyFile = ThisWorkbook.Path & Application.PathSeparator & Itm & ".PDF"
   
    '4)
       
    SourceWs.Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=11, Criteria1:=Itm
   
    LastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row
   
    SourceWs.Range("A1:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy
   
    '5)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Itm
        With ActiveSheet
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            With ActiveSheet.PageSetup
                .Zoom = False
                .Orientation = xlPortrait
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=MyFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
       
    End With
   
    Next
    '6)
    On Error Resume Next
    SourceWs.ShowAllData
    On Error GoTo 0
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   
End Sub

To add to this @Sequoyah, is there a way to limit the sheet name to the maximum number of allowed characters for a sheet name (the longest name I have is 50 characters long).

 

[Pestomania]

I think I fixed the errors of the limit levels, but not the invalid characters for a name.

Is tehre a way to do this to make it by "site" & "-" & "Bldg"?

In the case where "Bldg" is blank, just put it by site name?

I tried but keep getting a key element of the collection error.

Sub File_by_Building()

'https://www.mrexcel.com/board/threads/employee-roster-separation.1265153/

    Dim SourceWs         As Worksheet
    Dim cel         As Range, myRng As Range
    Dim Bldg         As Variant
    Dim Site        As Variant
    Dim LastRow     As Long
    Dim CollBldg   As New Collection
    Dim collSite    As New Collection
    Dim MyFile As String
    
    Set SourceWs = ThisWorkbook.Sheets("Employee_Roster") '<<============ ADAPT Sheet name
        
    On Error Resume Next
    '1)
    ActiveSheet.ListObjects("user").Range.AutoFilter Field:=9
    ActiveSheet.ShowAllData
    SourceWs.Range("A1:J" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
    
    For Each cel In SourceWs.Range("H2:H" & Range("H" & Rows.Count).End(xlUp).Row)
        CollBldg.Add cel.Value, CStr(cel.Value)
    Next
    On Error GoTo 0
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    '2)
    For Each Bldg In CollBldg

    For Each cel In SourceWs.Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row)
        collSite.Add cel.Value, CStr(cel.Value)
    Next
    On Error GoTo 0
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    '3)
    For Each Site In CollBldg
    
    '4)
    MyFile = ThisWorkbook.Path & Application.PathSeparator & Site & "-" & Bldg & ".PDF"
    
    '5)
    
    SourceWs.Range("A1:J" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=9, Criteria1:=Bldg
    
    LastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row
    
    SourceWs.Range("A1:J" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    
    '6)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Left(Bldg, 31)
        With ActiveSheet
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            With ActiveSheet.PageSetup
                .Zoom = False
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            With ActiveSheet
                    Columns("A:A").Select
                    Selection.ColumnWidth = 7.14
                    Columns("B:B").Select
                    Selection.ColumnWidth = 53.43
                    Columns("C:C").Select
                    Selection.ColumnWidth = 5.86
                    Columns("D:D").Select
                    Selection.ColumnWidth = 6.14
                    Columns("E:E").Select
                    Selection.ColumnWidth = 16.86
                    Columns("F:F").Select
                    Selection.ColumnWidth = 19.71
                    Columns("G:G").Select
                    Selection.ColumnWidth = 8.43
                    Columns("H:H").Select
                    Selection.ColumnWidth = 15.43
                    Columns("I:I").Select
                    Selection.ColumnWidth = 44
                    Columns("J:J").ColumnWidth = 43
                    Columns("C:H").Select
                    Selection.VerticalAlignment = xlCenter
                End With
                    With Selection
                        .WrapText = False
                        .Orientation = 0
                        .AddIndent = False
                        .ShrinkToFit = True
                        .ReadingOrder = xlContext
                        .MergeCells = False
                    End With
            .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=MyFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
        
    End With
    
    Next
    Next
    '7)
    On Error Resume Next
    SourceWs.ShowAllData
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

 

[Sequoyah]

Hi @Pestomania,
For the issue of illegal characters and parentheses as per your request in post #7, try the following macro modified with the addition of a function that replaces illegal characters and the content inside parentheses. For all other requests, I would kindly ask you to open a new thread

Option Explicit

Sub Pestomania2()
'https://www.mrexcel.com/board/threads/employee-roster-separation.1265153/

    Dim SourceWs         As Worksheet
    Dim cel         As Range, myRng As Range
    Dim Itm         As Variant
    Dim LastRow     As Long
    Dim collMgr   As New Collection
    Dim MyFile As String
   
    Set SourceWs = ThisWorkbook.Sheets("Sheet1") '<<============ ADAPT Sheet name
       
    On Error Resume Next
    '1)
    For Each cel In SourceWs.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        collMgr.Add cel.Value, CStr(cel.Value)
               
    Next
    On Error GoTo 0
   
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    '2)
     
    For Each Itm In collMgr
    '4)
       
    SourceWs.Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=11, Criteria1:=Itm
       
    LastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row
   
     '3)
    'MyFile = ThisWorkbook.Path & Application.PathSeparator & Itm & ".PDF"
    MyFile = ThisWorkbook.Path & Application.PathSeparator & CleanFileName(Itm) & ".PDF"
   
    SourceWs.Range("A1:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy
   
    '5)
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Itm
        With ActiveSheet
            .Range("A1").PasteSpecial Paste:=xlPasteFormats
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            With ActiveSheet.PageSetup
                .Zoom = False
                .Orientation = xlPortrait
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=MyFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
       
    End With
       
    Next
   
    '6)
    On Error Resume Next
    SourceWs.ShowAllData
    On Error GoTo 0
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   
End Sub

Function CleanFileName(Itm As Variant) As String
 
    Dim InvalidChars As String
    InvalidChars = "\/:*?""<>|"
   
    Dim i As Integer
    For i = 1 To Len(InvalidChars)
        Itm = Replace(Itm, Mid(InvalidChars, i, 1), "")
    Next i

       Do While InStr(Itm, "(") > 0 And InStr(Itm, ")") > 0
        Itm = Left(Itm, InStr(Itm, "(") - 1) & Mid(Itm, InStr(Itm, ")") + 1)
    Loop

        Itm = Trim(Itm)
   
    CleanFileName = Itm
End Function