PDA

View Full Version : Solved: creating multiple xltx templates



Beatrix
10-09-2012, 09:20 AM
Hi Everyone ,

I have an xlsx file and need to produce multiple xltx templates by using main workbook. There are 54 users and each template is assigned to a specific user. Also templates are named in specific format. Please see below:

H-POD_S1_v02.(username should be written in specific cell. M8=Rachel etc)
H-POD_S2_v02.
H-POD_S54_v02.

I maintain master copy regularly and end up creating multiple templates manually whenever I made some changes on main copy. As an Excel VBA lover I've thought I should stop doing it this way and automate the process to save time.

wondering if there is a VBA script ready to use for similar data scenario?

Cheers
Yeliz

mancubus
10-10-2012, 06:05 AM
Hi, Yeliz,

create one xltx file. inform the users of the location and the name of the file.
let them open and save their files.
everone is happy..



or try xlsx files from master.xlsx
something like this...

the workbook containing below code has a sheet named "users".
colum A in "users" contain user_names and B contains real names, without column heads. should you wish col heads change For i = 1 to 54 to For i = 2 to 55...
if new users will be added to the list then it's better to use a variable for last row.



Sub make_files()

Dim wb As Workbook
Dim User_Code As String, User_Name As String
Const fPath As String = "C:\xxx\yyy\fff\"

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Calc = .Calculation
.Calculation = xlCalculationManual
End With

For i = 1 To 54
User_Code = ThisWorkbook.Worksheets("users").Range("A" & i).Value
User_Name = ThisWorkbook.Worksheets("users").Range("B" & i).Value
Set wb = Workbooks.Open(fPath & "master.xlsm")
wb.ActiveSheet.Range("M8") = User_Name
wb.SaveAs fPath & "H-POD_" & User_Code & "_v02", FileFormat:=51
wb.Close False
Next

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = Calc
End With

End Sub

Beatrix
10-10-2012, 10:36 AM
Thanks very much mancubus!:cloud9:

It's working perfect!:thumb I just need to get them as xltx instead of xlsx.

appreciate for your time :bow:

Cheers
Yeliz


Hi, Yeliz,

create one xltx file. inform the users of the location and the name of the file.
let them open and save their files.
everone is happy..



or try xlsx files from master.xlsx
something like this...

the workbook containing below code has a sheet named "users".
colum A in "users" contain user_names and B contains real names, without column heads. should you wish col heads change For i = 1 to 54 to For i = 2 to 55...
if new users will be added to the list then it's better to use a variable for last row.



Sub make_files()

Dim wb As Workbook
Dim User_Code As String, User_Name As String
Const fPath As String = "C:\xxx\yyy\fff\"

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Calc = .Calculation
.Calculation = xlCalculationManual
End With

For i = 1 To 54
User_Code = ThisWorkbook.Worksheets("users").Range("A" & i).Value
User_Name = ThisWorkbook.Worksheets("users").Range("B" & i).Value
Set wb = Workbooks.Open(fPath & "master.xlsm")
wb.ActiveSheet.Range("M8") = User_Name
wb.SaveAs fPath & "H-POD_" & User_Code & "_v02", FileFormat:=51
wb.Close False
Next

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = Calc
End With

End Sub

mancubus
10-10-2012, 11:24 AM
you're wellcome.

changing fileformat argument from 51 to 54 will do it.