Following problem:
There are some workbooks with 12 sheets each. these sheets are called:
"Jan 00", "Feb 00", ...,"Dec 00" or "Jan 03, ..., "Dec 03".

I know that I can refer to the 'internal' number of the sheets (1 to
12). But in my case I have to select the sheets by the
user-defined-name. The macro should exist over many years, so I can
only use part of the name = monthname.

I would like to have code like that:

If Sheets(Worksheets(x).Name).Name = "July 00" Then '(that works)

where the 00-part is variable!

Is there a possibility to refer only to a PART of the user defined
sheet name?

TIA Walt

You can use the Left function to get the first 3 letters,
for example:

If Left(Sheets(Worksheets(x).Name).Name,3) = "Jul" Then

Hope this helps,
Helen


-----Original Message-----
Following problem:
There are some workbooks with 12 sheets each. these
sheets are called:
"Jan 00", "Feb 00", ...,"Dec 00" or "Jan 03, ..., "Dec
03".

I know that I can refer to the 'internal' number of the
sheets (1 to
12). But in my case I have to select the sheets by the
user-defined-name. The macro should exist over many
years, so I can
only use part of the name = monthname.

I would like to have code like that:

If Sheets(Worksheets(x).Name).Name = "July 00"
Then '(that works)

where the 00-part is variable!

Is there a possibility to refer only to a PART of the
user defined
sheet name?

TIA Walt
.

Sub sheetmonth()'recommend all at 3 letters Jul 00
For Each Sh In Sheets
If UCase(Left(Sh.Name, 3)) = "JUL" Then
Sh.Select 'or whatever you want to do
end if
Next
End Sub

--
Don Guillett
SalesAid Software
Granite Shoals, TX

"Walt" wrote in message
om...
Following problem:
There are some workbooks with 12 sheets each. these sheets are called:
"Jan 00", "Feb 00", ...,"Dec 00" or "Jan 03, ..., "Dec 03".

I know that I can refer to the 'internal' number of the sheets (1 to
12). But in my case I have to select the sheets by the
user-defined-name. The macro should exist over many years, so I can
only use part of the name = monthname.

I would like to have code like that:

If Sheets(Worksheets(x).Name).Name = "July 00" Then '(that works)

where the 00-part is variable!

Is there a possibility to refer only to a PART of the user defined
sheet name?

TIA Walt

You can write a function like this:

Function SheetByName(ByVal NamePattern As String) As
Worksheet
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If UCase(sh.Name) Like UCase(NamePattern) Then
Set SheetByName = sh
Exit For
End If
Next sh
End Function

and then use it:

Public Sub Test()
Dim MyWorkSheet As Worksheet
Dim Pattern As String
Pattern = "feb*"
Set MyWorkSheet = SheetByName(Pattern)
If MyWorkSheet Is Nothing Then
MsgBox "No sheets matching '" & Pattern & "' found",
vbCritical
Else
MyWorkSheet.Activate
' some other code goes here
Set MyWorkSheet = Nothing
End If
End Sub

-----Original Message-----

Is there a possibility to refer only to a PART of the
user defined
sheet name?

Walt,

This is untested, but might work.

Just add a way to input mywsh (InputBox, ComboBox, ListBox, Drop Down, etc.)

Dim x as Integer, wsh as Integer, mywsh as String

mywsh = "July 05"
For x = 1 to ActiveWorkbook.Worksheets.Count
If Lcase(Sheets(x).Name) = Lcase(Left(mywsh,3)) then
Sheets(x).Select
Exit For
End If
Next

You could also use a Do .... While .... Loop

steve

"Walt" wrote in message
om...
Following problem:
There are some workbooks with 12 sheets each. these sheets are called:
"Jan 00", "Feb 00", ...,"Dec 00" or "Jan 03, ..., "Dec 03".

I know that I can refer to the 'internal' number of the sheets (1 to
12). But in my case I have to select the sheets by the
user-defined-name. The macro should exist over many years, so I can
only use part of the name = monthname.

I would like to have code like that:

If Sheets(Worksheets(x).Name).Name = "July 00" Then '(that works)

where the 00-part is variable!

Is there a possibility to refer only to a PART of the user defined
sheet name?

TIA Walt

THX!!!

Walt