Hi,
I was given this script below in answer to a previous question by Bob
Phillips (thanks Bob) . Here is my previous question :
"if a log for 'today' has already been created, what code do I (well you!)
need to add to advise the user of this, and a message box asking them if they
want to view it,then being taken there if required.,
thanks again"
the script Bob provided is
Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
On Error Resume Next
Set sh = Worksheets(Format(Date, "YYYY-MM-DD"))
On Error Goto 0
If Not sh Is Nothing Then
ans = Msgbox("Sheet already exists - view it?", vbYesNo)
If ans = vbYes Then
sh.ACtivate
End If
Else
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "YYYY-MM-DD")
.Visible = False
End If
End With
Application.ScreenUpdating = True
End Sub

....but on trying this the macro stops and places the error at :

If Not sh Is Nothing Then

Can anybody resole this as I am new and haven't got a clue!
thanks

Anthony,

I have just tried it again, and it works fine for me.

A couple of questions.

On the day you ran it, did that day's worksheet already exist?
What error dialog did you get back?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Anthony" wrote in message
...
Hi,
I was given this script below in answer to a previous question by Bob
Phillips (thanks Bob) . Here is my previous question :
"if a log for 'today' has already been created, what code do I (well you!)
need to add to advise the user of this, and a message box asking them if
they
want to view it,then being taken there if required.,
thanks again"
the script Bob provided is
Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
On Error Resume Next
Set sh = Worksheets(Format(Date, "YYYY-MM-DD"))
On Error Goto 0
If Not sh Is Nothing Then
ans = Msgbox("Sheet already exists - view it?", vbYesNo)
If ans = vbYes Then
sh.ACtivate
End If
Else
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "YYYY-MM-DD")
.Visible = False
End If
End With
Application.ScreenUpdating = True
End Sub

...but on trying this the macro stops and places the error at :

If Not sh Is Nothing Then

Can anybody resole this as I am new and haven't got a clue!
thanks

Sorry Bob - I do appreciate ur help but now I'm very confused,
maybe if I explain 'the whole picture' you can come up with some better code??
I have a menu on my personal toolbar with 3 options
1) create new log
2) view today's log
3) vire historic log

so what I requie is :-
in option1 for the user to be able to create a log, which is a copy of my
'master log' worksheet and rename this new sheet as today's date. However if
a log has already been opened for 'today' a msg box advising the user so and
if they wish to view it.
in option2 for the user to be able to view todays log (ie a search for a log
named 'todays date') - again if one not found then a msg box advising so and
a further option to create one.in option3 a msg box to alow the user to input
a date eg 13Jul and a search is carried out for that named worksheet if found
it is displayed - if not a msg box advising so.

I Hope this has made my final goal more understanable and I would be very
very very gratful for any code you could provide.
Thanks for your understanding
Anthony

"Bob Phillips" wrote:

Anthony,

I have just tried it again, and it works fine for me.

A couple of questions.

On the day you ran it, did that day's worksheet already exist?
What error dialog did you get back?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Anthony" wrote in message
...
Hi,
I was given this script below in answer to a previous question by Bob
Phillips (thanks Bob) . Here is my previous question :
"if a log for 'today' has already been created, what code do I (well you!)
need to add to advise the user of this, and a message box asking them if
they
want to view it,then being taken there if required.,
thanks again"
the script Bob provided is
Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
On Error Resume Next
Set sh = Worksheets(Format(Date, "YYYY-MM-DD"))
On Error Goto 0
If Not sh Is Nothing Then
ans = Msgbox("Sheet already exists - view it?", vbYesNo)
If ans = vbYes Then
sh.ACtivate
End If
Else
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "YYYY-MM-DD")
.Visible = False
End If
End With
Application.ScreenUpdating = True
End Sub

...but on trying this the macro stops and places the error at :

If Not sh Is Nothing Then

Can anybody resole this as I am new and haven't got a clue!
thanks