Hi Team, I need your help. I have names in the column A. I need to replicate another simple excel base file by as many files as the names are in the column A and title them by names in the column A. I was using the below macros. On the small sample testing it went very well, however, when I went live, turned out that some of the newly created files titles unexplainably were changes with adding some (002) or (003) to new file titles. I can't understand the association for that. Can you please fix my macros, or help me with another " clean" macros. THANK YOU for all your help!!!
Sub test()
Dim fso As Object, fName As String
Dim fPathOld As String, Filename As String
Dim sExt As String, x As Long, myPath As String
fPathOld = "C:UsersabgaryanaDesktop
rsp test2018 OAIA RRSP Transfer Form_Formulaire de transfert de la PIAO 2018 au REER.xlsx"
'Change as per your requirement
fName = Dir(fPathOld)
myPath = Mid(fPathOld, 1, InStr(1, fPathOld, fName) - 1)
sExt = Mid(fName, InStr(1, fName, "."), 5)
Set fso = CreateObject("Scripting.FileSystemObject")
ThisWorkbook.Sheets("sheet1").Activate
For x = 2 To Range("A" & Rows.Count).End(xlUp).Row
Filename = Cells(x, 1).Value & sExt
fso.Copyfile fPathOld, myPath & Filename
Next x
End Sub
Hi Anna,
Can you please share your workbook with data.
I thought you are maybe trying to overwrite a file that already exists, hence the 002, 003 etc added tothe names. But the default for fso.Copyfile is to overwrite a file if it already exists so I'm not sure what is happening for you.
Regards
Phil
Thank you Philip,
So I have an excel file - I am attaching it - a form that needs to be re-created as many times as user names that I have in another file with a name title for each file. Lets say I have Anna in my list, I will need a copy of that file with name Anna, etc until all the names have a copy of that file.
I found this code in the web, I am not proficient with coding, so I can't really read it and understand what it exactly mean, however it somehow worked if not that weird thing with numbering.
So I am attaching the form to be replicated and the file with names and macro.
Please help me and thank you!
Hi Anna,
The name of the file you uploaded and the name in the code weren't the same, so once I amended the code, this worked fine. There were only 2 names in the workbook so I can only test for those 2, but I had no problems.
Just one note though, your code uses Range("A" & Rows.Count).End(xlUp).Row which gives the number 525, which is the row your table ends on. But you of course only want the rows with names in.
Regards
Phil
