Report

Create new excel files based on the data in a column

Ask a question ExcelTrey 1Posts Monday March 13, 2017Registration date March 14, 2017 Last seen - Last answered on Mar 17, 2017 at 08:31 AM by vcoolio
Hello,

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 
Helpful
+0
plus moins
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
            rng.Copy
            Workbooks.Add
            ActiveSheet.Paste
            ActiveSheet.Columns.AutoFit
            ActiveSheet.[A1].Select
            ActiveWorkbook.SaveAs Filename:=mypath & .Range("F" & nr).Value & ".xlsx"
            ActiveWorkbook.Close
        End If
    Next
           .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.

Cheerio,
vcoolio.

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:-

https://www.eileenslounge.com/viewtopic.php?f=27&t=24342
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!