convert list to sheet tabs (2003 SP3)

[steveh]

Evening all

I have 8 existing worksheets and I would like to add another 61, emp1:emp61. I have created a list on a worksheet highlighted it and then run the following code

Code: Select all

Sub NameTabs()
Dim N As Integer
 On Error Resume Next
 For Each x In Selection
     N = N + 1
     Sheets(N).Name = x
 Next
End Sub

But all that is happening is the 8 existing tabs are renamed emp1:emp8. Any examples please of how to add the new sheets and rename them as required?

[HansV]

Unlike in fantasy novels, naming something will not magically create it in Excel. Try this:

Code: Select all

Sub NameTabs()
  For Each x In Selection
    Worksheets.Add.Name = x
  Next x
End Sub

[steveh]

Unlike in fantasy novels, naming something will not magically create it in Excel. Try this:

Code: Select all

Sub NameTabs()
  For Each x In Selection
    Workheets.Add.Name = x
  Next x
End Sub

Hi Hans

I must be reading the wrong novels

Using the code I get run time error 424 - Object required? The MS help on it was as clear as mud to me I am afraid

[HansV]

Sorry, a typo. Workheets should be Worksheets.

[steveh]

Sorry, a typo. Workheets should be Worksheets.

Fantastic

Thanks Hans, and it runs so quick!