create a sheet named after a range name VBA

[tektripper]

I am using excel 5 and am trying to use VBA to name a sheet after a range name or cell value. I keep getting the "Runtime error '13' Type mismatch error.

for example here is some of my code

RangeName = ActiveCell.value 'say is equal to Jan'
myRange.Name = RangeName

in the immediate window
?RangeName
Jan
?myRange.Name
=SheetName!$A$2:$K$67

shouldn't myRange.Name = RangeName = ActiveCell.value = Jan

although the immediate window shows this discrepency, the macro works OK, however if i try to assign the sheetname = range name i recieve the error. Can somebody provide me with some rules that need to be followed if i want to assign cell values or range names to variables which i then want to use as sheet names.

Hope i have made some sense. Please ask me to clarify if you might be able to help.

Thanks

 

[DaleWatson123321]

Hi tektripper,

I'm tested the following code, and it works. I use Excel 97, but I expect the code should still work with Excel 5.

For this code to work, it naturally requires that you FIRST create a Range Name for the cell in which you will enter (or have entered) the name you wish to use to create the sheet name.

Dim sheet_name As String

Sub Create_SheetName()
sheet_name = Range("sh_name&quot.Value
ActiveSheet.Name = sheet_name
End Sub

A note of caution: The way you phrased your question suggests that you might have attempted to name a sheet after a Range Name. I would strongly recommend AGAINST using Range Names which are the SAME as sheet names. Such practice can possibly lead to later confusion (for yourself as well as for Excel).

Please advise if the VBA code works. Thanks.

Regards, ....Dale Watson dwatson@bsi.gov.mb.ca