Save template using list of filenames

co

New Member
Joined
Jan 30, 2004
Messages
42
Hello all,

I have a one sheet template which I need to save as 200 new separate files.
On a separate spreadsheet I have a list of the 200 cost centres they need to be saved as.

Is there a macro to automatically saveas and rename the tab for each one to avoid me doing this manually?

Many thanks in advance,

co
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Assume that the cost centre names are in a workbook named "CostCentres", Sheet1, Range "A1:A200".

Assume that Sheet1 of the template workbook will get the cost centre name.

In the `ThisWorkbook' module of the template workbook paste this code and run the procedure:

Code:
Sub CreateCostCentreFiles()
    Dim CCNames As Range
    Dim i As Integer
    Set CCNames = Workbooks("CostCentres").Sheets(1).[a1:a200]
    For i = 1 To 200
        With ActiveWorkbook
            .Sheets(1).Name = CCNames.Cells(i)
            .SaveAs CCNames.Cells(i)
        End With
    Next i
        
End Sub
 
Upvote 0
Thanks for the reply.

When I run the code the following error appears: Subscript out of range

Any ideas?

Cheers,

co
 
Upvote 0
You must change all the range references, sheet numbers and book names to the actual data. If you still face a problem, post again giving the details of you sheet/book names and actual ranges holding the data.
 
Upvote 0
File with data in: CostCentres.xls Sheet1 a1:a200

File to be merged with: Template.xls

I've copied the data and template to new files as above but still no go.

co
 
Upvote 0
Sorry to ask a foolish question. Is the CostCentres file open when you run the macro? (It has to be).
 
Upvote 0
Yes it is. I wasn't sure either way, so I tried it both open and closed.

The error 'Subscript out of range' seems to relate to this line:

Set CCNames = Workbooks("CostCentres").Sheets(1).[a1:a200]

I've named the workbook CostCentres and there is nothing else going on with the spreadsheet except for the data in Sheet1 A1:A200.

They're saved in a separate directory.

I've checked the spellings of what I've changed on the spreadsheets, so I don't know!

Cheers,

co
 
Upvote 0
FYI:

Cost Centre Spreadsheet:<html>
CostCentres.xls
ABCD
1A0004
2A0006
3A0007
4A0021
5A0024
6A0026
7A0034
8A0035
9A0036
10A0039
Sheet1
</html>

Template Spreadsheet:<html>
Template.xls
ABCD
1RENEWALS PROGRAMME
2LANDOR HOUSE - I0030
3
4
5
6CURRENTREQUIRED
7ESTIMATEDANNUAL
8ITEMCOSTYEARSPROVISION
9
10
1150
1250
13100
Sheet1
</html>
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top