Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking worksheet names
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking worksheet names
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 "C Brehm" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking worksheet names
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) "C Brehm" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
how to copy workbook names and worksheet names to columns in acces | Excel Programming | |||
Checking names on correct line across sheets | Excel Worksheet Functions | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Checking range names | Excel Programming |