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

 

[iliace]

Let's say the file names are in the current workbook, Sheet1, A1:A200. You have a file called template.xls in C:\My Documents, and you want to save your new files in C:\My Documents.

Public Sub SaveTemplate()
  Const strSavePath As String = "C:\My Documents\"
  Const strTemplatePath As String = "C:\My Documents\template.xls"

  Dim rngNames As Excel.Range
  Dim rng As Excel.Range
  Dim wkbTemplate As Excel.Workbook
  
  Set rngNames = ThisWorkbook.Worksheets("Sheet1").Range("A1:A200").Values
  
  Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)
  
  For Each rng In rngNames.Cells
    wkbTemplate.SaveAs strSavePath & rng.Value
  Next rng
  
  wkbTemplate.Close SaveChanges:=False
End Sub

 

[dmdek]

Thank you very much for helping me -

When I run the macro I rec this message on the following line

"Run time error 438
Object doesn't support this property or method "

[Set rngNames = ThisWorkbook.Worksheets("Sheet1").Range("A1:A200").Values]

Could you advise how I should proceed.

Dan

 

[iliace]

My apologies - it should be "Cells", not "Values". In fact, you can remove the last word and the last period.

 

[dmdek]

I cannot thank you enough -


It works like a charm -


If I had told you how much time I have spent trying to figure this process out -

Thanks again

Dan

 

[iliace]

Glad it worked - best of luck to you!

 

[kunal86]

I want to use the same template file and then "save as" renaming the file 100+, but The file names will pick up their names from a one column list & save as to destination from another column list. .e.g.
[TABLE="width: 583"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]masterTemplete path[/TD]
[TD]Save Path[/TD]
[TD]Workbook name[/TD]
[/TR]
[TR]
[TD]C:\Users\Desktop\audit sheet\[/TD]
[TD]C:\Users\Desktop\Joheb\[/TD]
[TD]Maya[/TD]
[/TR]
[TR]
[TD]C:\Users\Desktop\audit sheet\[/TD]
[TD]C:\Users\Desktop\Kunal\[/TD]
[TD]Aniket[/TD]
[/TR]
[TR]
[TD]C:\Users\Desktop\audit sheet\[/TD]
[TD]C:\Users\Desktop\Aakash\[/TD]
[TD]Sufi[/TD]
[/TR]
</tbody>[/TABLE]



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

 

[amaresh achar]

hi iliace.. am amaresh...

I tried your code.. which works perfect..!!! Thankyou very much...

But i need your help in its next stage...

1. Now I have a name in the col A (where file name for template will be extracted)..

2. And i have some details in col B, col C, col D,.... upto , col M. (Which is corresponding details of each row)

3. Now when i run the macro.. the files will be generated...

4. Say a file generated from the excel cell "A3".... Now.. I want to copy the details of that row (i.e. details from B3, C3, D3, E3, F3, G3, H3, I3, J3, K3, L3, M3) to different cells in the file generated from cell "A3"

Please I need your help in this very badly.

Thank You in Advance
Amaresh

 

[iliace]

hi iliace.. am amaresh...

I tried your code.. which works perfect..!!! Thankyou very much...

But i need your help in its next stage...

1. Now I have a name in the col A (where file name for template will be extracted)..

2. And i have some details in col B, col C, col D,.... upto , col M. (Which is corresponding details of each row)

3. Now when i run the macro.. the files will be generated...

4. Say a file generated from the excel cell "A3".... Now.. I want to copy the details of that row (i.e. details from B3, C3, D3, E3, F3, G3, H3, I3, J3, K3, L3, M3) to different cells in the file generated from cell "A3"

Please I need your help in this very badly.

Thank You in Advance
Amaresh

Amaresh,

You may be able to get away with code like this:

Public Sub SaveTemplate()
  Const strSavePath As String = "C:\My Documents\"
  Const strTemplatePath As String = "C:\My Documents\template.xls"


  Dim rngNames As Excel.Range
  Dim rng As Excel.Range
  Dim wkbTemplate As Excel.Workbook
  
  Set rngNames = ThisWorkbook.Worksheets("Sheet1").Range("A1:A200").Values
  
  Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)
  
  For Each rng In rngNames.Cells
	wkbTemplate.Worksheets("Sheet1").Cells(1).Value = wkbTemplate.Range("B3:M3")
    wkbTemplate.SaveAs strSavePath & rng.Value
  Next rng
  
  wkbTemplate.Close SaveChanges:=False
End Sub

 

[amaresh achar]

Thank you very much for your quick response sir...

I tried the code with modifications as u suggested...

Am getting a run time error '438'
Object doesn't support this property or method

And I need to copy the information from those individual cell to be copied to different cells in the template.. I.e b3 of current sheet to a11 of template , c3 to d6 etc.