select sheets by name - how?

  • Thread starter Thread starter Walt
  • Start date Start date
W

Walt

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
 
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
 
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
 
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
 
Back
Top