Create Multiple Files using Template with Files Names in a Column List

[dmdek]

Could someone please assist with a problem I cannot think through. I want to use 1 template file and then "save as" renaming the file 200+.

The file names will pick up their names from a column list. I was hoping the macro will have a variable so that I can indicate where the files should be stored.

I cannot figure out how to loop through the cells in the column to pick up the file names.


Thank you in advance.

Dan



Excel 2003

 

[Akhi]

That's what .Offset(0, 1) does - offset 0 rows, 1 column. So if rng is A2, then rng.Offset(0,1) is B2.

It shows following error

What to be done?

 

[iliace]

Oh, my bad. It needs a sheet. Will the worksheet with H16 always be the same?

For example,

wkbTemplate.Worksheets("Sheet1").Range("H16") = rng.Offset(0, 1).Value

or if it's always the first sheet

wkbTemplate.Worksheets(1).Range("H16") = rng.Offset(0, 1).Value

something along those lines.

 

[Lleuadwen]

I have followed the code and it works for about 7 files then stops bugging our at the saveAs part, any ideas?

TIA

 

[iliace]

Sorry, it's been a while. Can you post the version of the code that you're using, as there have been multiple iterations above.

Also, when you saying "bugging" does it display an error message, or something else happens (e.g. freeze, crash)? If you get an error also post that.

 

[Lleuadwen]

Thanks for your response

I used the original one that you posted years ago. However, I found them all this morning in My Documents which was not the path I had in the code. I will faff with it and see if I can get it to create them where they should be.

 

[Lleuadwen]

I got it to work and it is brilliant, this is what I am using

Public Sub CreateFiles()

Const StrSavePath As String = "C:\Users\Amorr\OneDrive\Personal\iGCSE 4IT1_02\2306\My Allocation\94044"
Const strTemplatePath As String = "C:\Users\Amorr\OneDrive\Personal\iGCSE 4IT1_02\2306\My Allocation\4IT_02_Calculated_Marking_Spreadsheet_2306 New Version 3.xlsm"


Dim rngNames As Excel.Range
Dim wkbTemplate As Excel.Workbook
Dim rng As Excel.Range


Set rngNames = ThisWorkbook.Worksheets("Centre Number").Range("A10:A12")
Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)

For Each rng In rngNames.Cells

wkbTemplate.SaveAs StrSavePath & "\" & "4IT_02_" & rng.Value & "_531696"

Next rng

wkbTemplate.Close SaveChanges:=False

End Sub

However, I have a favour the tail end of the save path (94044) changes depending on the centre number, also the range changes depending on the length of the list, any ideas how to sort this out please?

 

[iliace]

Depending on how the centre number changes, but you probably need to parametrize that portion of the path. In other words, if you have:

Const StrSavePath As String = "C:\Users\Amorr\OneDrive\Personal\iGCSE 4IT1_02\2306\My Allocation\"

and then if your centre number is to the right of your file name portion (i.e. B10:B12) you might have something like this in the loop:

For Each rng In rngNames.Cells

  wkbTemplate.SaveAs StrSavePath & rng.Offset(0, 1).Value & "\" & "4IT_02_" & rng.Value & "_531696"

Next rng

There is a number of ways you can approach a variable length list, for instance if it always starts in A10, you could use:

With ThisWorkbook.Worksheets("Centre Number")
  Set rngNames = Range(.Range("A10"), .Range("A10").End(xlDown))
End With

You might also consider a dynamic named range, using a Table (i.e. structured range), etc. If using a table, you'd have to modify the code slightly using a ListObject data type.

 

[Lleuadwen]

Thank you so much. I did get it working using the range method and it was so much easier. Thank you for your help