worksheets(1).Name gives an error

[macroNubee]

hi,
does anyone know why the statment

Worksheets(1).Name

give an error even when that sheet exists.
It works fine on most of my workbooks, but
in some workbooks, I can run

MsgBox Worksheets.Count

and know there are worksheets in the workbook, but I get an error when I try to access any of their names.

Thanx

 

[Tommy Bak]

Hi
you probably have a space in the name.
this function (alone) can't handle that.

regards Tommy

 

[Mudface]

Works OK for me, what error are you getting? Remember that Worksheets(1) refers to the first worksheet in your workbook, regardless of when it was added. To refer to a sheet explicitly, try using Sheet1 (the actual name will be in the VBA explorer) or Worksheets("Worksheet_Name"), but remember that you will get an error if you try and rename a sheet to the name of a sheet that already exists.

 

[Mudface]

On 2002-04-19 10:38, Tommy Bak wrote:
Hi
you probably have a space in the name.
this function (alone) can't handle that.

regards Tommy

Sorry Tommy, it doesn't matter whether it has a space in there or not.

 

[KniteMare]

I've noticed this behaviour as well. So I use Sheets for WorkSheets. It works both ways in the attached code. You could also be missing a object reference statement like With or Set. VBA statements may be written in a multitude of ways and still work, but miss one little letter and NADA!!

Also:
What did your Error statement tell you.
What was the Error Number and What was the text?

Option Explicit

Sub WorksheetNm()
Dim sheetnm As String
sheetnm = Worksheets(1).Name
MsgBox Sheets.Count
MsgBox Worksheets.Count
MsgBox (sheetnm)
End Sub


Yours in EXCELent Frustration
KniteMare

 

[macroNubee]

thanks for your responses, but the
Worksheets(1).Name
method call does work for workbooks that have sheets named with spaces in them, it just doesn't seem to work for a specific file even though it does have two sheets in it. The following is the actual code:

For Each ws In Worksheets
If Trim(ws.Name) = "mySheet" Then
found = 1
End If
Next ws

the error says:

"Application defined or object defined error"

 

[Tommy Bak]

Mudface > sorry, you are off caurse right. Space doesn't matter in this case.
It's just that I recently had a problem regarding names, but know I come to think of that, it was i the indirect function.



regards Tommy

 

[Jay Petrulis]

On 2002-04-19 10:50, macroNubee wrote:
thanks for your responses, but the
Worksheets(1).Name
method call does work for workbooks that have sheets named with spaces in them, it just doesn't seem to work for a specific file even though it does have two sheets in it. The following is the actual code:

For Each ws In Worksheets
If Trim(ws.Name) = "mySheet" Then
found = 1
End If
Next ws

the error says:

"Application defined or object defined error"

How did you dimension the variables? The following worked for me.

------------------
Sub test()
Dim ws As Worksheet, found As Byte

For Each ws In Worksheets
If Trim(ws.Name) = "mySheet" Then
found = 1
End If
Next ws
MsgBox found
End Sub
-------------------