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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.