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

dmdek

New Member
Joined
May 3, 2011
Messages
4
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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.

Code:
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

New Member
Joined
May 3, 2011
Messages
4
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

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
My apologies - it should be "Cells", not "Values". In fact, you can remove the last word and the last period.
 
Last edited:

dmdek

New Member
Joined
May 3, 2011
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Glad it worked - best of luck to you!
 

kunal86

New Member
Joined
Apr 20, 2015
Messages
3

ADVERTISEMENT

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.
masterTemplete pathSave PathWorkbook name
C:\Users\Desktop\audit sheet\C:\Users\Desktop\Joheb\Maya
C:\Users\Desktop\audit sheet\C:\Users\Desktop\Kunal\Aniket
C:\Users\Desktop\audit sheet\C:\Users\Desktop\Aakash\Sufi

<colgroup><col><col><col></colgroup><tbody>
</tbody>



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

amaresh achar

New Member
Joined
Dec 9, 2016
Messages
48
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

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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:
Rich (BB code):
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

New Member
Joined
Dec 9, 2016
Messages
48
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,358
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top