search worksheets

[Guest]

Hi, I am new to this, so this may well be a simple question.

I have this code which I want to seach all my worksheets in my workbook for
a worksheet which is called after today's date, ie if executed now it would
search for a worksheet named '14Jul'
here's the code I have

Sub Goto_Todays_log()
SheetName = "today()"
If sSheetName = "" Then Exit Sub
On Error Resume Next
Sheets(sSheetName).Select
On Error GoTo 0
End Sub

...but it doesn't work because it is seaching for a worksheet called "today()"

can anybody help - aLso if possible I wud like a msg box telling the user
that one hasn't been found with todays date if one not found

many thanks

 

[Guest]

hi.
Change it so something like this.....
Sub mactestfindsheet()
Dim sSheetName As String
sSheetName = InputBox("Enter a worksheet name to find.")
If sSheetName = "" Then Exit Sub
On Error Resume Next
Sheets(sSheetName).Select
On Error GoTo 0
End Sub

good luck
FSt1

 

[Guest]

Hi thanks for help,
is it possible to have some code that will automatically find the worksheet
named after todays date rather than the user inputing it??
thanks

 

[Guest]

hi,
don't think so. to find a date the sheet name would have to be a
recognizable date and sheet naming convictions do not allow colons, slashes,
questions marks asterics or brackets. dates are usually written 7/13/05(with
slashes)
also in your example you had sheet name 14jul. this would not be recognized
as a date.
sorry
FSt1

 

[Guest]

Ok,thats fine, thanks for explaining it,
whilst I have you can I just expand the question a little?
when the user inputs a 'date' to find if a worksheet with that 'date' is not
found can you also give me some code for this with a msg box like
"not found, either create one or enter another date!
thanks again
Anthony

 

[Guest]

hi,
yes, change the code i first suggested to this
Sub mactestfindsheet()
Dim sSheetName As String
sSheetName = InputBox("Enter a worksheet name to find.")
If sSheetName = "" Then Exit Sub
On Error Resume Next
Sheets(sSheetName).Select
If Selection = "" then msgbox ( sSheetname & " not found.")
On Error GoTo 0
End Sub

regards
FSt1

 

[Guest]

Hi,
almost there..it kinda worked, but if I entered todays date '14Jul' the
worksheet with this name is opened, but the msg box saying 14Jul not found is
also shown ??
thanks

 

[Guest]

If your sheets are consistently named MMMdd then you can set the search
string up automatically:

Sub FindSheet()
Dim strName As String
strName = WorksheetFunction.Text(Date, "MMMdd")
Err.Clear
On Error Resume Next
Worksheets(strName).Activate
If Err.Number <> 0 Then
MsgBox strName & " not found"
Exit Sub
End If

End Sub

 

[Guest]

hi again,
I posted too soon.
instead of this line
If sSheetName = "" Then Exit Sub
put this instead

if sSheetName = "" then
msgbox ("No Iput! Find sheet aborted.")
exit sub
end if

sorry. should have thought that out more.
FSt1

 

[Guest]

Fantastic, at last the solution I wanted - - - - - thanks !!!!

one other related question Duke

I have this code which searches for historic worksheets with a given 'date
name' as i'm not good at this I can't get it to run.
what I need is for a msg box asking user to input worksheet 'date' required,
search for it - display it if found or a msg box saying 'date' not found and
they have to ceate one - here is my efort

Sub Search_daily_log()
Dim sSheetName As String
Dim Foundflag As Boolean
Dim MySheet
sSheetName = "today()"
sSheetName = InputBox("Please type in the date you wish to look at using the
first 3 letters of the required month - eg 12Jul or 12Sep ")
If sSheetName = "" Then Exit Sub
Sheets(sSheetName).Select
If Foundflag = False Then
MsgBox "Sorry, can't find a log for" & Format(Date, "DDMMM") & " please
create one!"
End If
End Sub

many thanks

 

[Guest]

First - the code I gave you earlier has the dates in the wrong format. change

strName = WorksheetFunction.Text(Date, "MMMdd")

to

strName = WorksheetFunction.Text(Date, "ddMMM")

Now, try this instead of your code

Sub Search_daily_log()
Dim sSheetName As String

sSheetName = InputBox("Please type in the date you wish to look at using the
first 3 letters of the required month - eg 12Jul or 12Sep ")
If sSheetName = "" Then Exit Sub

err.clear
on error resume next
Sheets(sSheetName).Activate
if err.number <> 0 then
MsgBox "Sorry, can't find a log for" & Format(Date, "DDMMM") & " please
create one!"
End If

End Sub

 

[Guest]

FSt1, and Duke
man ymany thanks to you both for sorting this out, I think its all working
just fine now....................