Unsure How to Proceed

[NKlawender]

I have a spreadsheet with 8 columns but a varying number of rows. Column E is employee name and column F is their manager. not all employees are associated with a manager. I am trying to write a macro to create a folder for each manager, then copy each individual instructors data and save it as a separate excel file in their managers folder. If an employee isn't associated with a manager, I still want to create a separate excel file with their data and place it in the same location as the manager folders. I have started by sorting the employees alphabetically, but thats as far as I can get. I am not sure how to attack the rest of it.
Thanks,
Norm

 

[nemmi69]

This will set your manager folders up for you

Dim WBook As Workbook
Dim Wsheet As Worksheet
Dim LastManager As Long
Dim ManLoop As Long
Dim ManName As String
Dim sFolderPath As String

Function ValidName(StrName As String) As Boolean
Dim ChkArr()
Dim ValidLoop As Long
Dim ValChr As Integer
Dim ErrMsg As String

ChkArr = Array("<", ">", ":", ";", Chr(34), "/", "\", "|", "?", "&", "*", ".", "$")
ValidName = False

StrName = Trim(StrName)
' Check 0-31 ASCII characters
If Asc(StrName) <= 31 Then
    ErrMsg = "Found invalid characters below ASCII 32 for a folder name."
    GoTo InvalidName
End If
' Check Windows invalid characters
For ValChr = LBound(ChkArr) To UBound(ChkArr)
    If InStr(StrName, ChkArr(ValChr)) > 0 Then
        ErrMsg = "Found invalid ASCII character '" & ChkArr(ValChr) & "' for a folder name."
        GoTo InvalidName
        Exit Function
    End If
Next ValChr
ValidName = True
Exit Function

InvalidName:
MsgBox ErrMsg, vbCritical, ThisWorkbook.Name

End Function

Sub ManagerFolder()
Set WBook = ThisWorkbook
Set Wsheet = WBook.ActiveSheet

' Find last row of manager names
LastManager = Range("E1").End(xlDown).Row
sFolderPath = "C:\Managers\" ' Set this as the primary folder that the manager folders will be created in
If Right(sFolderPath, 1) <> "\" Then
    sFolderPath = sFolderPath & "\"
End If
If Dir(sFolderPath, vbDirectory) = vbNullString Then
    MsgBox "Primary folder '" & sFolderPath & "' does not exist.", vbCritical, ThisWorkbook.Name
    Exit Sub
End If
' If primary folder valid

' Create folders for manager names that do not already exist
For ManLoop = 1 To LastManager
    ManName = Wsheet.Range("E" & ManLoop).Value
    'Validate Folder name
    If ValidName(ManName) = True Then
        'Primary folder does exist, but does contain manager folder name
        If Dir(sFolderPath & "\" & ManName, vbDirectory) = vbNullString Then
            ' Folder does not exist so create
            Application.ScreenUpdating = False
            MkDir sFolderPath & "\" & ManName
            Application.ScreenUpdating = True
            If Dir(sFolderPath & "\" & ManName, vbDirectory) = vbNullString Then
                MsgBox "Error creating folder '" & sFolderPath & "\" & ManName & "' program stopped.", vbCritical, ThisWorkbook.Name
                Exit Sub
            End If
        Else
            ' Any code for a folder already exists
        End If
    Else
        ' Choose what to happen on fail
    End If
Next ManLoop
End Sub