Programmatically Rename Excel Sheets

Quote

Originally posted by Sam
I have a workbook project that generates a large number of new spreadsheets each time it is run.

I would like to programmatically rename these spreadsheets as they are created, using a value found in cell A1.

Can anyone give me an example of how this would be done?

Thanks in advance -

Sam

Display More

This will name the activesheet whatever value is found in cell A1 of that sheet:

ActiveSheet.Name = ActiveSheet.Range("A1").Value

This will name the second sheet in the workbook whatever value is found in cell A1 of the first worksheet:

Sheets(2).Name = Sheets(1).Range("A1").Value

Hope this helps you out.

I am sure you realize that you will not be able to name all of your sheets based on one static name in A1, Excel will not allow you to name your sheets the same. I am assuming you are placing an array of names to be inserted or have some counter to add to the end of your names. Something similar to;

Sub ChangeWSName()

Dim i As Integer
Dim WSCount As Integer
WSCount = Worksheets.Count

Do While i < WSCount

i = i + 1
Sheets(i).Name = Range("A1").Value & i

Loop

End Sub

Bruce