creating 37 workbooks with names based on cells

[timwe]

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

 

[BQardi]

You're welcome (both of you)...

 

[LockeGarmin]

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\"

Just a minor tip, A change I would suggest for this and future code would be the following:

SaveFolder = Environ("UserProfile") & "\Desktop\Procurement"

SaveFolder = Environ("UserProfile") & "\Desktop\Procurement[COLOR=#FF0000][B]\[/B][/COLOR]"

Admittedly most Windows computers these days probably have their profile on C:\Users so in most cases this would end up being the same thing but it's not guaranteed and this could potentially avoid a bug down the road.

 

[Veritan]

That's a good point. I was assuming that the file path would be changed anyways, so I just used a convenient one as a place holder, but yes, that's a better coding procedure in the end.

 

[LockeGarmin]

Ah, you're probably right. When I saw that the problem had been solved I didn't read his case very carefully, saw that little bit of code and thought the tip might help. Guess I should read more carefully.

 

[timwe]

thank you BQuardi