use VBA to create and name workbooks based on cell data

[jimoneill]

HI,

I have a workbook which i use to register clients (name; address; registration date, etc.). One of the cells uses =concatenate() to generate a unique Id for each client. I want to automate the creation of a personal workbook for each client, using data from the unique Id cell, as the filename for the newly created workbook and save it in a new location. The following code works perfectly to create, name and save the new workbook with the unique Id and in the appropriate folder.

However, workbook created here is based on the Blank Template; what i really want is for the new workbook to be based on an existing template which i have already designed.

Sub AddNew()
Dim wbName As String
wbName = Selection.Value
Dim newBook As Workbook
Set newBook = Workbooks.Add
With newBook
.Title = wbName
.SaveAs filename:="K:\SUPPORT\CHT-Training\TestFilenameFolder\" & wbName, fileFormat:=52
End With
End Sub

I've trawled google search results and youtube and a variety of VBA books in vain for a week now and can't find any working examples of this.

any help would be much appreciated, thanks in advance

Jim

 

[Mindpsyche]

HI,

I have a workbook which i use to register clients (name; address; registration date, etc.). One of the cells uses =concatenate() to generate a unique Id for each client. I want to automate the creation of a personal workbook for each client, using data from the unique Id cell, as the filename for the newly created workbook and save it in a new location. The following code works perfectly to create, name and save the new workbook with the unique Id and in the appropriate folder.

However, workbook created here is based on the Blank Template; what i really want is for the new workbook to be based on an existing template which i have already designed.

Sub AddNew()
Dim wbName As String
wbName = Selection.Value
Dim newBook As Workbook
Set newBook = Workbooks.Add
With newBook
.Title = wbName
.SaveAs filename:="K:\SUPPORT\CHT-Training\TestFilenameFolder\" & wbName, fileFormat:=52
End With
End Sub

I've trawled google search results and youtube and a variety of VBA books in vain for a week now and can't find any working examples of this.

any help would be much appreciated, thanks in advance

Jim

If the file in which your unique id's are in is the same file for the template, why don't you just try a "Save As" on the current file to achieve what you need?

 

[jimoneill]

The files are completely different. The first file, which constructs the unique Id, is a register of all clients i deal with. Each client then needs a separate "Report Card" which allows me to monitor their progress through our Training programs.

 

[Mindpsyche]

Ok here is another possibility i think:

Firstly you must have a blank template lying on a known location (The template you want to use).

Activate the macro from your first file in order to obtain the unique customer id. Then open the template and do a "Save as" on the template on the location you want it under using the unique customer id in the name? Then close the newly made file.

 

[jimoneill]

you seem to be suggesting to do this manually. kinda defeats the purpose of VBA, doesn't it?

 

[Mindpsyche]

I probably explained incorrectly. I meant to explain the logic of the macro, not for you to do it manually but rather the steps the macro should take.

Run Macro

Macro Steps:
1. Store Unique Customer Id in variable
2. Open template from fixed path
3. "Save as" on preffered location using the Variable in the file name
4. Close file

Macro stops

This is what I meant.