I need to generate several hundred identical excel workbooks using a macro. I'd like to standardise the file names based on a list in Excel. So if column A says 'AC.334.997' and column B says 'Process improvement project', it would generate workbooks based on the template - in this case named 'AC.334.997_Process improvement project', and so on.
Sub CreateWBs()
Dim lRow, x As Integer
Dim wbName As String
lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1
Do
x = x + 1
wbName = Range("A" & x).Value & "_" & Range("B" & x).Value
ActiveWorkbook.SaveAs Filename:="C:\Documents\" & wbName & ".xls"
Loop Until x = lRow
End Sub
Change the bold part to your own destination directory.
Running the code will save your workbook under a different name mentioned in columns A and B. It will keep renaming until there is no more data in columns A and B and thus creating more workbooks in the destination folder.
This works well - the only issue is that each workbook it creates is a duplicate of the original list whereas what I need is for each workbook to be a given template... any idea how to do that?
Could I specify a template or a location of a file that it will then take as a sample and copy for each individual file that it creates?
Fantastic work - thanks again to both of you. I've got this working now which is excellent. I do have a related question which would be the icing on the cake if we could get this up and running:
I've already got quite a bit of information about each of the schemes and what I'd like to do is self populate each file with this data. So let's say we add a 'Priority score' in column C alongside the ID number and scheme name, and ask the macro to populate that information in a specific cell in each of the files it is saving.
So as well as having a file for each scheme, you also have the relevant priority score in a specified cell. I've got quite a bit of information of this sort, but someone can show me the principle, I can expand for other columns or other information I've already got.
Hi,
I've had a couple of issues this morning in testing and trying to get it to work. It creates a couple of new workbooks no problem, then stops with the message:
"Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed"
... then asking me to de-bug.
I'm wondering whether some of the file names are too long due to the project names being too long - is there a way we could take just the first 20 characters of the project name (column B)? Any other suggestions?
Option Explicit
Sub copyTemplate()
Dim lRow, x As Integer
Dim wbName As String
Dim fso As Variant
Dim dic As Variant
Dim colA As String
Dim colB As String
Dim colSep As String
Dim copyFile As String
Dim copyTo As String
Set dic = CreateObject("Scripting.Dictionary") 'dictionary to ensure that duplicates are not created
Set fso = CreateObject("Scripting.FileSystemObject") 'file scripting object for fiile system manipulation
colSep = "_" 'separater between values of col A and col B for file name
dic.Add colSep, vbNullString ' ensuring that we never create a file when both columns are blank in between
'get last used row in col A
lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1
copyFile = "C:\Document\Template\Termplate.xls" 'template file to copy
copyTo = "C:\Document\projects\" 'location where copied files need to be copied
Do
x = x + 1
colA = Range("A" & x).Value 'col a value
colB = Range("B" & x).Value ' col b value
colB = Left(Range("B" & x).Value, 20) 'only retain first 20 characters
wbName = colA & colSep & colB ' create new file name
If (Not dic.Exists(wbName)) Then 'ensure that we have not created this file name before
fso.copyFile copyFile, copyTo & wbName & ".xls" 'copy the file
dic.Add wbName, vbNullString 'add to dictionary that we have created this file
End If
Loop Until x = lRow
Set dic = Nothing ' clean up
Set fso = Nothing ' clean up
End Sub
Is it possible to combine the two separate Sub's? For example, I have a list of filenames I want the template to be named in column A and then in column B I have a list alongside all of the file names containing a value to be input into a specific cell on the template file prior to saving with column A's filename. Basically, I would like the list separate from my template file to be replicated. I wouldn't want the list showing up in any of my replicated workbooks.
Most easy way would be to pop up a input box where user may enter the folder to save the files. This in simplest form will suffer from GIGO (garbage in , garbage out)
Other way would be to have a dialog box where user navigate to the place. If you do search, you should find example for both cases
Hi, thanks for your code, could you tell me how to modification your code to get new value name workbooks from other sheets. Could you show me how to fix my problem?
Can you modify this code for the following requirement:
I have created 908 different files with different names from an excel list, having the same template. However I also need to populate the first 3 cells (A2, B2 and C2) of each of these files with values in that same excel list corresponding to the filenames.
Could you create a new thread (short threads are better then long ones) and explain in greater detail what you would like to achieve?
Which code did you use? You would like to fill A2, B2, C2 with values, where can those values be found?
6182 users have said thank you to us this month
