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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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
 

co

New Member
Joined
Jan 30, 2004
Messages
42
Thanks for the reply.

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

Any ideas?

Cheers,

co
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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.
 

co

New Member
Joined
Jan 30, 2004
Messages
42

ADVERTISEMENT

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
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Sorry to ask a foolish question. Is the CostCentres file open when you run the macro? (It has to be).
 

co

New Member
Joined
Jan 30, 2004
Messages
42

ADVERTISEMENT

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
 

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
Does

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

work any better?
 

co

New Member
Joined
Jan 30, 2004
Messages
42
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>
 

co

New Member
Joined
Jan 30, 2004
Messages
42
tmischler said:
Does

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

work any better?

No, I get the same error......

Cheers,

co
 

Forum statistics

Threads
1,147,666
Messages
5,742,510
Members
423,734
Latest member
123hmMission

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