Worksheet Name rules

[Dan Wilson]

Good day. I'm working on a workbook for a friend who is using Excel 2003. I wrote some macros to sort his data. There are 12 worksheets in the workbook and the name of one of them is two words with a space character between. When I try to write a macro using this worksheet name, it won't accept the worksheet name because of the space. How do I get around this besides obviouosly deleting the space in the name? I tried using a dash character, but that won't work either. Are there some rules about naming worksheets? I searched "Learn Excel 2007", but found nothing to help.

As always, help is appreciated.
Thanks, Dan...

 

[Nalani]

You may run into other Macro problems if the user changes the name of the Worksheet Tab and your Macro is making reference to the "Assigned Name"

Read this thread that lenze posted some time ago. It may help you out:
http://www.mrexcel.com/forum/showpost.php?p=2078095&postcount=6

 

[niluj]

Does this help?

Sheets.Add.Name = "This Is A Sheet Name"
Sheets("This Is A Sheet Name").Select

 

[Dan Wilson]

Good day Niluj. Thank you for responding to my request. I'm sure that your fix will work within the macro, but the problem is in creating the macro in the first place. The first thing asked for when recording a macro is a name for the macro. This name does not allow any spaces or special characters.

 

[Nalani]

As a follow-up on providing a direct answer to your question:

Without seeing your code it is hard tell tell how you are refering to the Worksheet.

With this "test" and a sheet named My Sheet (with a space in the name) and some other sheet being active at the time of Run:

Sub myTest()
ActiveWorkbook.Sheets("My Sheet").Select
End Sub

My Sheet becomes the active sheet.

Maybe a view of your code would help to resolve your issue.

 

[Dan Wilson]

Good day Nalani. Thank you for responding to my request. I will review the article that you referenced.
Thanks, Dan...

 

[Nalani]

Good day Niluj. Thank you for responding to my request. I'm sure that your fix will work within the macro, but the problem is in creating the macro in the first place. The first thing asked for when recording a macro is a name for the macro. This name does not allow any spaces or special characters.

The Name of the Macro will not allow Spaces. Therefore the workaround is the Underscore.

Sub my_Test()
code.........
End Sub

 

[Nalani]

The Name of the Macro will not allow Spaces. Therefore the workaround is the Underscore.

Sub my_Test()
code.........
End Sub

You thread was about Worksheet Names. But it seems that you may be having trouble with Nameing Macros as well.

Above will take care of Spaces. But keep in mind that you should not use "reserved" words for Macro Names either.

Example"

Sub Delete() if you wanted to say, delete some Rows

Instead:

Sub myDelete() or

Sub rDelete()

Hope this helps

 

[Dan Wilson]

Good day again Nalani. Thank you for your continued help. Your last reply solved the problem. I was trying too hard. The name of the macro has nothing to do with what the macro does. I knew that the underscore had to fill in any blank spaces, but thought that the worksheet name had to be kept as it was. The fix is to replace the space in the worksheet name with an underscore in the macro title, and then use the workhseet name as it is inside the macro itself. I tend to make things more difficult than they need to be sometimes. I think all is worling now.

Thanks again, Dan...