Problem naming worksheets 1

[jnix]

I'm trying to uase a macro to name worksheets based on a name entered in a cell on the worksheet. My macro looks like this:

Sub Worksheet_Name()



Worksheets("Base&quot.Name = Range("K1&quot
Worksheets("1st Quarter&quot.Name = Range("K1&quot & " 1st Quarter"
Worksheets("2nd Quarter&quot.Name = Range("K1&quot & " 2nd Quarter"
Worksheets("3rd Quarter&quot.Name = Range("K1&quot & " 3rd Quarter"
Worksheets("4th Quarter&quot.Name = Range("K1&quot & " 4th Quarter"


End Sub

It works for the first four worksheets, but then gives me run-time 9 error, subscript out of range on the fifth worksheet. What's my problem and how can I correct it?
Thanks,
jnix

 

[SkipVought]

Hi,

Usually a subscript error means that the sheet specified does not exist. Check the sheet name carefully!

BTW, here's how I would do it...

for each ws in worksheets
  with ws
    select case .Name
      case "Base"
        .Name = Range("K1")
      case Else
        .Name = Range("K1") & .Name
    end select
  end with
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

a few things...

range k1 cannot contain any excel reserve words (a reserve word would be any function that excel uses ,"add sum date and so on)


check the format of range K1 on that sheet...does it match the other sheets that workk?


no special charcters are allowed ("/","&","."...and the like)

 

[ETID]

Yeah,..like skip said you might have an extra space somewhere in the sheet name.

 

[CajunCenturion]

I think Skip is right in that the Worksheet you specified does not exist, and it may be because the name is not exactly spelled correctly. But it may also be because the sheet does not exist at all.

One thing you can look at is the WorkBook.Worksheets.Count property to see how many sheets you have. If it fails on the 5th worksheet, and WorkBook.Worksheets.Count is 5 or greater, then you almost suredly have a spelling error. But if WorkBook.Worksheets.Count is 4 or less, then the worksheet simply does not exist. In this case, you can Add a new worksheet to the workbook, or otherwise skip the processing of that sheet.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein