Splitting a Worsheet into multiple files based on Cell Value

[Phil_Lloyd_1979]

I have a report which details multiple fields and one of the columns is manager name.

This report is 3,000 rows long however I am looking to create a separate file for each individual line manager.

I understand a code can be written into VBA which when run will split the sheet into multiple files.

Any hekp on this matter would be greatkly appreciated.

Phil

[Fotis1991]

I had another same issue some time ago. See if this helps you.

Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String

    Set wSheetStart = ActiveSheet
    wSheetStart.AutoFilterMode = False
    'Set a range variable to the correct item column
    Set rRange = Range("A1", Range("A65536").End(xlUp))
    
        'Delete any sheet called "UniqueList"
        'Turn off run time errors & delete alert
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("UniqueList").Delete
        
        'Add a sheet called "UniqueList"
        Worksheets.Add().Name = "UniqueList"
        
           'Filter the Set range so only a unique list is created
            With Worksheets("UniqueList")
                rRange.AdvancedFilter xlFilterCopy, , _
                 Worksheets("UniqueList").Range("A1"), True
                 
                 'Set a range variable to the unique list, less the heading.
                 Set rRange = .Range("A2", .Range("A65536").End(xlUp))
            End With
            
            On Error Resume Next
            With wSheetStart
                For Each rCell In rRange
                  strText = rCell
                 .Range("A1").AutoFilter 1, strText
                    Worksheets(strText).Delete
                    'Add a sheet named as content of rCell
                    Worksheets.Add().Name = strText
                    'Copy the visible filtered range _
                    (default of Copy Method) and leave hidden rows
                    .UsedRange.Copy Destination:=ActiveSheet.Range("A1")
                    ActiveSheet.Cells.Columns.AutoFit
                Next rCell
            End With
            
        With wSheetStart
            .AutoFilterMode = False
            .Activate
        End With
        
        On Error GoTo 0
        Application.DisplayAlerts = True
End Sub

[Phil_Lloyd_1979]

Hi Fotis,

Many thanks for your help. The code you provide actually splits the sheet into multiple sheetsm, I am looking to create separate files. Also, the value I am basing this split on is in Column M.

Hope you are able to look into this for me.

Regards

Phil

[Fotis1991]

Follow the link Phil.

http://www.excelforum.com/excel-prog...n-country.html

[Fotis1991]

Phil

Holger's code in that thread is easy to modified. Insist of column A that both of us used there you have to use column M.

If you can not handle this, then upload a small sample workbook to show you how to do this.

[Phil_Lloyd_1979]

Hi Fotis,

I have amended the references to ranges A to M but this is not working however I moved my column from M to A which worked.

If I add the line manager email address is it possible to add to the macro to create an email, add text and add the respective file?

Regards

Phil

[Fotis1991]

Phil sorry but i don't know(i am talking about your new questions).

But as this is something different from your original question, you can mark this thread as solved and start a new thread with your new questions.

Good luck.

[Phil_Lloyd_1979]

Hi Fotis,

I can't see the mark solved option.

Regards

Phil

[Fotis1991]

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

[Phil_Lloyd_1979]

Hi Fotis,

Further to the above case we have a similar reauest which relates to the above but goes one step further. We want the sheets to be split but then to add a password to each split file. Is there any code to this effect that we can add to the macro?

Regards

Phil