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


"Duke Carey" wrote:

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


"Anthony" wrote:

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



"Duke Carey" wrote:

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

"Anthony" wrote:

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