Rename Workheet Names in File

[WESTERNWALL]

Hi

I have workbooks in which there a number of worksheets. Each time I save a new file from the previous one I need to change the names on each worksheet tab. Each name is different. Some are days of the month, for instance. In that example I have 31 tabs that need to be renamed 01 March, 02 March, etc. Possibly have a list of tab names (that can be changed at will) on a separate worksheet that are Range Named and link that to the Macro. Just a thought.

Many thanks

Western

 

[Fluff]

Maybe something like

Sub westernwall()
   Dim Ws As Worksheet
   Dim i As Long
   
   i = 1
   For Each Ws In Worksheets
      Ws.Name = Range("List")(i)
      i = i + 1
   Next Ws
End Sub

 

[WESTERNWALL]

Hi Fluff

Tried your code, but it's not working. I've placed and range-named a column of 31 cells, 01-Jan, 02-Jan, etc. on the first worksheet and called it List. I deleted Sheets 2 and 3. The error message says that there is a mistake in the name.

 

[Fluff]

Do you need the macro to create the sheets, or just rename existing sheets?

 

[WESTERNWALL]

OK! I worked it out. Thank you. I need 31 worksheets already in place and the contents of the range cannot be with characters like 01/03, etc. Only 01 March, etc works. That's great. However, if I do want to include characters would that change the code much?

Many thanks

West

 

[WESTERNWALL]

Excuse my idiocy. Can that be done? Silly question for Excel, right? That would help, of course. Does it matter if the names will include characters or not?

Tx

 

[Fluff]

This will create a new sheet for each cell in the list & will name them 01 March

Sub westernwall()
   Dim Cl As Range
   
   For Each Cl In Range("List")
      If Cl.Value <> "" Then
         Sheets.Add(, Sheets(Sheets.Count)).Name = Format(Range("List")(i), "dd-mmmm")
      End If
   Next Cl
End Sub

 

[WESTERNWALL]

G'morning Fluff

Thanks for your assistance.

However it's not working. Here it is:

-I've named the date Range "List" and it appears on the first worksheet. The dates are formatted to dd-mmmm. I've run the Macro with the original three worksheets and with only the first worksheet. The error highlights the Macro row starting with "Sheets.Add.........".

What am I doing wrong?

Tx

West

 

[Fluff]

What is the error message?

 

[WESTERNWALL]

Run-time error '1004:
Application-defined or object defined error