Macro for creating new workbooks from existing source file(excel

[Closed]
Report
Posts
1
Registration date
Monday November 21, 2016
Status
Member
Last seen
November 21, 2016
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Please provide the macro code for the following data source -

1 siddhesh 100000 09/02/1989
2 amol 100000 08/01/1991
3 siddhesh 150000 01/01/2012
4 rahul 100500 09/09/1989
5 amol 500000 01/02/1985
6 siddhesh 250000 21/11/2002
7 swapnil 300000 21/10/1995

Please provide the macro so that individual excel file can be created. For example "siddhesh.xls" will contain all the data named siddhesh, "amol.xls" will contain all the data named amol, "rahul.xls"will contain all the data named rahul.

1 reply

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Siddhesh,

Try the following code:-

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("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

With wks
    
For nrow = lr To 2 Step -1
        If (Not dic.exists(.Cells(nrow, "A").Value)) Then
        dic.Add .Cells(nrow, "A").Value, .Cells(nrow, "A").Value
        Set rng = .Range("A1:N" & .Cells(Rows.Count, 1).End(xlUp).Row)
            rng.AutoFilter field:=1, Criteria1:=.Range("A" & nrow).Value
            rng.Copy
            Workbooks.Add
            ActiveSheet.Paste
            ActiveSheet.Columns.AutoFit
            ActiveWorkbook.SaveAs Filename:=mypath & .Range("A" & nrow).Value & ".xlsx"
            ActiveWorkbook.Close
        End If
    Next
           .AutoFilterMode = False
End With

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

End Sub


The code assumes that the names are in Column A and it will create a new work book for each name in Column A with the relevant data for each individual being transferred to each individual work book.

Each new work book will be stored in the same folder as the originating work book.

Do you really want to create a new work book for each individual? It is quite possible that your list of names could become very long and creating a new work book for each individual could become a major drain on resource. Would you not prefer to have one work book with a separate work sheet for each individual?

I hope that this helps.

Cheerio,
vcoolio.