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

 

[Tom.Jones]

@Fluff,

Here is a variable "i" ??

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")([COLOR=#ff0000][B]i[/B][/COLOR]), "dd-mmmm")
      End If
   Next Cl
End Sub

 

[Fluff]

Good spot Tom
It should be

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

 

[WESTERNWALL]

Bang on! Many thanks Fluff and Tom.Jones. Glad combo, guys. Much appreciated.

West

 

[Fluff]

Glad we could help & thanks for the feedback