Create new excel files based on the data in a column [Closed]

ExcelTrey 1 Posts Monday March 13, 2017Registration date March 14, 2017 Last seen - Mar 14, 2017 at 06:56 PM - Latest reply: vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen
- Mar 17, 2017 at 08:31 AM

I've spent hours searching and trying different macros with no avail. I'm not new to excel, but new to macros and coding. I have a spreadsheet with departmental data that needs separated by managers name, so it may be distributed to each with only their teams data. Essentially, I need a file for each manager and it to include the corresponding data. The managers name is in column "F". For this week, I have 57 rows of data and columns range from A to I. Is there a macro or a code to use in VBE that I can run to do this automatically? I know I can use sort & filter to copy and paste the data into each additional file but I'm hoping for a macro that will be able to take care of this. Thank you, any help is appreciated.

See more 

1 reply

vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Updated by vcoolio on 17/03/17 at 08:40 AM
Hello ExcelTrey,

Try the following code (untested),assigned to a button and in a copy of your workbook first:-

Sub CreateNewWbks()

    Dim dic As Object, rng As Range, wks As Worksheet, mypath As String, lr As Long
    Set dic = CreateObject("scripting.dictionary")
    Set wks = Sheet1
mypath = ThisWorkbook.Path & "\"
lr = wks.Range("F" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With wks
For nr = lr To 2 Step -1
        If (Not dic.exists(.Cells(nr, "F").Value)) Then
        dic.Add .Cells(nr, "F").Value, .Cells(nr, "F").Value
        Set rng = .Range("A1:I" & .Cells(Rows.Count, 1).End(xlUp).Row)
            rng.AutoFilter field:=6, Criteria1:=.Range("F" & nr).Value
            ActiveWorkbook.SaveAs Filename:=mypath & .Range("F" & nr).Value & ".xlsx"
        End If
           .AutoFilterMode = False
End With

MsgBox "All Done!", vbExclamation
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

The code should create a new file for each name in Column F with the relevant rows of data for each name being transferred to the relevant file. Each new file will be saved in the same folder as your main file.

I hope that this helps.


P.S.: You may be interested in the following query in another forum. Its amazingly close to your own query and has a very good resolution clearly set out:-