Help with splitting an Excel File in to Many using VBA

[Dianna Zueff]

Hi, I viewed an awesome tutorial today by Chandoo relating to creating a VBA to separate one workbook into many.
Split an Excel File in to Many using VBA
In my case its by supervisor. I have a few issues. The code is working, but i would like to modify it to do a few more things...

1. on the new files created, I need the columns to retain the column width and formatting from the master sheet so when the supervisor opens the file, they can see all the data completely without resizing the columns. Also, formatted to print 1 page x 1 page landscape with narrow margins.

2. I need to have a header or extra rows to put in WITH the company logo and the title of the report on the files being created by the VBA.

3. I need to omit the last 2 column (Daily rate (Column Y) & Total (Column Z)

4. I would like to omit column E (Supervisor) as they don't need to see their own names. However this is the column I use to separate the report, so I'm not sure if this is possible.

5. I would like the files to be attached to an email for the supervisors automatically. This is not required just would be nice.

I think this would make my project perfect for implementation.

Option Explicit

Sub breakMyList()
    ' This macro takes values in the range myList
    ' and breaks it in to multiple lists
    ' and saves them to separate files.
  
    Dim cell As Range
    Dim curPath As String
  
    curPath = ActiveWorkbook.Path & "\"
  
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
  
    For Each cell In Range("Istsalesman")
        [valSalesman] = cell.Value
        Range("myList").AdvancedFilter Action:=xlFilterCopy, _
            criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
        Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
        Workbooks.Add
        ActiveSheet.Paste
        ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Sheet1.Name & " Vacation" & Format(Now, "yyyy") & ".xlsx", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
        Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents
    Next cell
  
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
  
End Sub

__________________________________________________________________

Mod edit : thread moved to appropriate forum !​