Macro for creating new workbooks from existing source file(excel
Closed
siddhesh_sjoshi
Posts
1
Registration date
Monday November 21, 2016
Status
Member
Last seen
November 21, 2016
-
Nov 21, 2016 at 12:18 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Nov 21, 2016 at 09:16 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Nov 21, 2016 at 09:16 PM
Related:
- Macro for creating new workbooks from existing source file(excel
- Spell number in excel without macro - Guide
- Windows 10 iso file download 64-bit - Download - Windows
- Safari view source - Guide
- Download macros for excel - Download - Spreadsheets
- Gta 5 exe file download for pc - Download - Action and adventure
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Nov 21, 2016 at 09:16 PM
Nov 21, 2016 at 09:16 PM
Hello Siddhesh,
Try the following code:-
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.
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.
