Checking worksheet names

[C Brehm]

Have the folowing code hat works, but sheet name may not exsit.
All worksheets with data start with "Sales " and end in a year
i.e. "Sales 2005"
Need to get first year of worksheets and last year and make the years
between first year and last year only valid for years that exsit in
workbook.
How would I check for worksheet names and get Firstyear and Lastyear?


Do
SYear = CLng(Application.InputBox(Prompt:="Enter a year between 2005
and 2050", Default:=Year(Date), Type:=1))
' get start year for report
If SYear = 0 Then
Exit Sub 'give the user a way out??
Else
If SYear >= 2005 And SYear <= 2050 Then
Exit Do
End If
End If
Loop

 

[Rick Rothstein \(MVP - VB\)]

Does this subroutine help you?

Sub GetYears(StartYear As Long, EndYear As Long)
Dim X As Long
StartYear = Right(Worksheets(1).Name, 4)
EndYear = Right(Worksheets(1).Name, 4)
For X = 2 To Worksheets.Count
If Worksheets(X).Name Like "Sales ####" Then
If Right(Worksheets(X).Name, 4) < StartYear Then
StartYear = Right(Worksheets(X).Name, 4)
End If
If Right(Worksheets(X).Name, 4) > EndYear Then
EndYear = Right(Worksheets(X).Name, 4)
End If
End If
Next
End Sub

You can use it like this from within your own code...

Sub Test()
Dim FirstYear As Long
Dim LastYear As Long
GetYears FirstYear, LastYear
MsgBox "Year range: " & FirstYear & " to " & LastYear
End Sub

Rick

 

[Bob Phillips]

Here is some code to extract the sheet number years

Dim FirstYear As Long
Dim LastYear As Long
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets

If Left(sh.Name, 6) = "Sales " Then

If FirstYear = 0 Or Val(Right$(sh.Name, 4)) < FirstYear Then

FirstYear = Val(Right$(sh.Name, 4))
End If
If LastYear = 0 Or Val(Right$(sh.Name, 4)) > LastYear Then

LastYear = Val(Right$(sh.Name, 4))
End If
End If
Next sh


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nigel]

One way.....


Sub MinMax()

Dim firstYear As Integer, lastYear As Integer
Dim wS As Worksheet, sheetYear As Integer

For Each wS In Worksheets
If Left(wS.Name, 5) = "Sales" Then
sheetYear = Val(Right(wS.Name, 4))
If firstYear = 0 Then firstYear = sheetYear
If sheetYear < firstYear Then firstYear = sheetYear
If sheetYear > lastYear Then lastYear = sheetYear
End If
Next

' useful check
MsgBox "First Year: " & firstYear & vbCrLf & _
"Last Year: " & lastYear

End Sub