creating 37 workbooks with names based on cells

timwe

New Member
Joined
Dec 22, 2016
Messages
12
Hi,

I have an Excel template that needs to be sent out to 37 different people/areas. I would like to duplicate this template without having to "save as" 37 times.

Can someone help with a VBA code that recreates the workbook 37 times and names it after a list of names in a column?

br
timwe
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hello timwe, and welcome to MrExcel. Couple of questions for you, is the list of names found in the same workbook as the template? If the names are in the same workbook, are they on the same tab as the Excel template you want to save, and would you only want to save the template and not the list of names? Also, are all 37 copies going to be saved to the same folder, or in different locations? If different, how are the folder locations differentiated?
 
Last edited:
Upvote 0
Here's some code that you should be able to adapt to what you need it for. If it's not working, please answer the questions in my earlier post.

Code:
Sub SaveTemplate()
    Dim SaveFolder$, SaveName$, arrNames(), i%
    
    SaveFolder = "C:\Users\" & Environ("UserName") & "\Desktop\Save Folder\"
    arrNames = Application.Transpose(Range("A1", Cells(Rows.Count, 1).End(xlUp)))
    For i = LBound(arrNames) To UBound(arrNames)
        SaveName = SaveFolder & arrNames(i) & ".xlsm"
        ThisWorkbook.SaveAs (SaveName)
    Next i
End Sub

I'm assuming that the names are in column A and are in the template that you want to save. The file path will obviously need updated to wherever you want to save the template to. Please note, since this utilizes VBA to save all the files, they will all be saved with a .xlsm file extension.
 
Upvote 0
Hi Veritan,

Thanks. To clarify, my workbook contains three sheets and I want all of them so I want to duplicate the whole workbook.
Regarding the file names, I have them in column U so I changed your code to that range.

I ran the code but get an error saying "subscript out of range" and in the debugger it refers to the following line:

SaveName = SaveFolder & arrNames(i) & ".xlsm"

The code that I ran looks like this:

Sub SaveTemplate()
Dim SaveFolder$, SaveName$, arrNames(), i%

SaveFolder = "C:\Users" & Environ("LNLD") & "\Desktop\Procurement"
arrNames = Application.Transpose(Range("U1", Cells(Rows.Count, 1).End(xlUp)))
For i = LBound(arrNames) To UBound(arrNames)
SaveName = SaveFolder & arrNames(i) & ".xlsm"
ThisWorkbook.SaveAs (SaveName)
Next i
End Sub


Probably something simple that I am missing but I am not familiar with VBA.
BR
 
Upvote 0
My first guess is the range of the array. Where you have Cells(Rows.Count, 1).End(xlUp), it actually needs to be Cells(Rows.Count, 21).End(xlUp), because the number there refers to the number of the column, and column U is the 21st column. See if that fixes it. Right now, it's trying to make a rather unusual looking range reference from U1 to A1 since column A is the 1st column.
 
Upvote 0
ok that fixed the range problem, but now it says "the file could not be accessed"

The folder I want it in is on my desktop, called Procurement. my username is LNLD. not sure what the issue is.
 
Upvote 0
You shouldn't change Environ("UserName"). This special function will "grab" the actual username from the computer where this code is run from.

Next you are missing some important backslashes, so change to this (exactly):
SaveFolder = "C:\Users\" & Environ("UserName") & "\Desktop\Procurement"

If you don't have a backslash as the first character of each cell in column U, you also need a backslash after Procurement, like this:
SaveFolder = "C:\Users\" & Environ("UserName") & "\Desktop\Procurement\"
 
Last edited:
Upvote 0
You're welcome, and thanks to BQardi for the explanation about the username and filepath, you did an excellent job explaining the issues there.
 
Upvote 0

Forum statistics

Threads
1,216,105
Messages
6,128,859
Members
449,472
Latest member
ebc9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top