Macro to create new workbooks from master workbook

Do yo really need to separate this data, why not just filter by branch number or maube use a PivotTable/

Try this

Option Explicit



'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 24/09/2006 22:48
' Author    : Roy Cox (royUK)
' Website   :  more examples
' Purpose   :  Create a sheet for each unique name in data
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.
'             Adapted to copy to new workbook
'---------------------------------------------------------------------------------------

Sub ExtractToNewWorkBook()
    Dim ws     As Worksheet
    Dim rData  As Range
    Dim rCl    As Range
    Dim sNm    As String
    Set ws = Sheet1    ' make sure this is correct

    'extract a list of unique names
    'first clear existing list
    With ws    'change the ranges to suit
        Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 14).End(xlUp))
        .Columns(.Columns.Count).Clear
        .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

        For Each rCl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))
            sNm = rCl.Text
            'AutoFilter & copy to relevant sheet
            rData.AutoFilter Field:=1, Criteria1:=sNm
            Workbooks.Add
            ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & sNm & ".xls"
            rData.Copy Destination:=Workbooks(sNm).Sheets(1).Cells(1, 1)
            Workbooks(sNm).Close True
        Next rCl
    End With
    ws.Columns(Columns.Count).ClearContents        'remove temporary list
    rData.AutoFilter        'switch off AutoFilter
End Sub