I am trying to make a function that collects the name of a
sheet, and automatically fill in a cell on that sheet with
the name.

I'm trying something like:

Public Function Name() As String
On Error GoTo Skip
Back:
For x = 1 To 35
Name = Sheets(Sheet & x).Name
Next x
Skip:
x = x + 1
GoTo Back
End Function

Any help would be appreciated.

Thanks,
John

I assume this will be used in a worksheet

Public Function MyName()
Application.Volatile
MyName = Application.Caller.Parent.Name
End Function

It will only update when a calculation is performed.
--
Regards,
Tom Ogilvy

John Viall wrote in message
...
I am trying to make a function that collects the name of a
sheet, and automatically fill in a cell on that sheet with
the name.

I'm trying something like:

Public Function Name() As String
On Error GoTo Skip
Back:
For x = 1 To 35
Name = Sheets(Sheet & x).Name
Next x
Skip:
x = x + 1
GoTo Back
End Function

Any help would be appreciated.

Thanks,
John

Hi John:

Try:

Public Function SheetName() As String
SheetName = Application.Caller.Parent.Name
End Function

Regards,

Vasant.

"John Viall" wrote in message
...
I am trying to make a function that collects the name of a
sheet, and automatically fill in a cell on that sheet with
the name.

I'm trying something like:

Public Function Name() As String
On Error GoTo Skip
Back:
For x = 1 To 35
Name = Sheets(Sheet & x).Name
Next x
Skip:
x = x + 1
GoTo Back
End Function

Any help would be appreciated.

Thanks,
John