VB error - please help

[Guest]

Hi,
I have the code below that will create a new worksheet and rename it after
todays date. However if the user trys to create a log again , ie for the same
day, I get this error:-
Run-time error 1004
cannot rename a sheet to the same name as another sheet, a referenced object
or a workbook referenced by visual basic.

Oh course what I want is a msg box advising the user that one already
exsists ans asking if they wanna view it.

the code I have


Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "DDMMM")
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

any help appreciated

 

[Bob Phillips]

We have been here before.

I asked two questions, which you haven't answered.

This is the fourth thread you have started on this topic.

If you want help, you have to play your part.

--

HTH

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

 

[Guest]

Hi Bob
.....and I did reply to the last question..see here.....
http://www.microsoft.com/office/com...oc=en-us&dg=microsoft.public.excel.misc&fltr=

....but nobody has come forward with a reply...hence my frustration!!

Can you help??
thanks

 

[Bob Phillips]

Well I see 6 threads started by you on this topic since 17:11 on Sunday.

I gave you code which you say errors. It doesn't for me, yet you do not tell
me what the error is, just open the problem to a wider issue than originally
stated. Couple that with teh responses being scattered over 6 threads are
not endearing you. You are not going to get an answer (from me at least) if
you ignore what I ask and just keep firing off new threads.


--

HTH

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

 

[Guest]

Bob,
I thought I had given answers to your questions in the thread I pasted to
you in my last reply, however here they are again
now, in answer to your previous questions, if I create a log for today and
it has not already been done -all is fine, however if I try and create again
a pop up msg box says
"Run-time error 1004
Cannot rename a sheet to the same name as another sheet, a referenced object
libary or a workbook referenced by Visual Basic"

I then click on debug and the ActiveSheet.Name = Format(Date, "DDMMM") part
of the code is highlighted in yellow.
Sub Create_log()
Application.ScreenUpdating = False
With Sheets("Log master")
.Visible = True
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "DDMMM")
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

I am sorry for posting several times, but I thought my questions had been
'lost' in previous pages and nobody would revisit them. A lesson learnt there
for me then !

are you able to offer any other solutions??
thanks again