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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Veritan

Active Member
Joined
Jun 21, 2016
Messages
367
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:

Veritan

Active Member
Joined
Jun 21, 2016
Messages
367
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.
 

timwe

New Member
Joined
Dec 22, 2016
Messages
12

ADVERTISEMENT

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
 

Veritan

Active Member
Joined
Jun 21, 2016
Messages
367
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.
 

timwe

New Member
Joined
Dec 22, 2016
Messages
12

ADVERTISEMENT

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.
 

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
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:

Veritan

Active Member
Joined
Jun 21, 2016
Messages
367
You're welcome, and thanks to BQardi for the explanation about the username and filepath, you did an excellent job explaining the issues there.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top