search worksheets
 

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

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

"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

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

"FSt1" wrote:

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

"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

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

"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

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

"FSt1" wrote:

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

"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

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

"Anthony" wrote:

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

"FSt1" wrote:

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

"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

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

"FSt1" wrote:

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

"Anthony" wrote:

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

"FSt1" wrote:

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

"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

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

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

"FSt1" wrote:

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

"Anthony" wrote:

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

"FSt1" wrote:

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

"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

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

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

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