VB error - please help

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

0
Anthony2219 (255)
7/13/2005 9:34:03 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
300 Views

Similar Articles

[PageSpeed] 39

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)


"Anthony" <Anthony@discussions.microsoft.com> wrote in message
news:2C6D82CD-6352-4388-B488-9E368CF065EA@microsoft.com...
> 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
>


0
bob.phillips1 (6510)
7/13/2005 9:46:48 PM
Hi Bob
.....and I did reply to the last question..see here.....
http://www.microsoft.com/office/community/en-us/default.mspx?pg=2&guid=&sloc=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" wrote:

> 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)
> 
> 
> "Anthony" <Anthony@discussions.microsoft.com> wrote in message
> news:2C6D82CD-6352-4388-B488-9E368CF065EA@microsoft.com...
> > 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
> >
> 
> 
> 
0
Anthony2219 (255)
7/13/2005 9:58:01 PM
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)


"Anthony" <Anthony@discussions.microsoft.com> wrote in message
news:90F3418A-9509-4DF6-8561-349836FD48EA@microsoft.com...
> Hi Bob
> ....and I did reply to the last question..see here......
>
http://www.microsoft.com/office/community/en-us/default.mspx?pg=2&guid=&sloc=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" wrote:
>
> > 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)
> >
> >
> > "Anthony" <Anthony@discussions.microsoft.com> wrote in message
> > news:2C6D82CD-6352-4388-B488-9E368CF065EA@microsoft.com...
> > > 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
> > >
> >
> >
> >


0
bob.phillips1 (6510)
7/13/2005 10:36:33 PM
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

"Bob Phillips" wrote:

> 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)
> 
> 
> "Anthony" <Anthony@discussions.microsoft.com> wrote in message
> news:90F3418A-9509-4DF6-8561-349836FD48EA@microsoft.com...
> > Hi Bob
> > ....and I did reply to the last question..see here......
> >
> http://www.microsoft.com/office/community/en-us/default.mspx?pg=2&guid=&sloc=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" wrote:
> >
> > > 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)
> > >
> > >
> > > "Anthony" <Anthony@discussions.microsoft.com> wrote in message
> > > news:2C6D82CD-6352-4388-B488-9E368CF065EA@microsoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
> 
> 
> 
0
Anthony2219 (255)
7/13/2005 10:50:01 PM
Reply:

Similar Artilces:

Replication errors after adding new server
Customer has existing Windows 2003 server with Exchange 2003, plus varios other servers on the network. We recently added a new server with a view to retiring the existing server. Since adding the new server to the network we now get 4 Replication Errors every 5 minutes "Error 1129 occurred while processing a replication event" The 4 events relate to "NON_IPM_SUBTREE\SCHEDULE+ FREE BUSY", "NON_IPM_SUBTREE\OFFLINE ADDRESS BOOK", "NON_IPM_SUBTREE\EFORMS REGISTRY" & "NON_IPM_SUBTREE\Internet Newsgroups" We have not manually set any pub...

Internal App Error on Account Summary?
I've converted from Quicken. All's well, except for one investment account -- I can access 'everything' except for the Account Summary -- the page begins to load, freezes up, with a "Internal Application Error" message. Per MS Online Assisted Support -- I've tried to repair the file using both 'quick' and 'standard' repairs. When I reviewed the notepad that resulted from the standard repair, I found that it "Fixed Records in Online Table", but, the problem persists. Any ideas out there? I have a lot of transactions in this...

VB distmon?
Hi I have recently installed Office 2003. A couple of wired things happen in Excel: > Everytime I close the program I get a MS VB error that reads "Compile error in hidden module: Distmon" Every time open the program I get the same error, but the macro it is calling is "autoexecnew" > Since my new installation, I type in an simple function like TRIM and it tells me cannot find project library. any ideas? Thnaks! Alan Alan http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 OFF: "Compile Error in Hidden Module" Error Message When You Sta...

How to get rid of error with WFOLADIN.DLL? TIA!
I uninstalled WinFax but I get an error message when I open Outlook 2003 saying "C:\Doc...etc..\wfoladin.dll is not a valid Office AddIn." I looked in the folder though and the file doesn't exist any more. Suggestions on how to get rid of the error message? Much thanks!!! Remove the WinFax Pro extensions in the Add-in Manager (Tools > Options > Other > Advanced Options...) -- Russ Valentine [MVP-Outlook] "scripteez" <scripteez@yahoo.com> wrote in message news:3gkd105q664tsocnhsak0f12h1cnttc3nl@4ax.com... > I uninstalled WinFax but I get an error me...

http error 501
Hy everybody, I would like to ask if someone has any clue about my problem. When I'm triing to create new public folder I'll ger the error mentioned in subject. This problem is discused in Article ID 839744, but it doesn't help:-( If someone knows something please share your knowledge. Thank You very much. ...

SQL_Login script error, wrong arguments to system 1339
I've got a consultant who does demos for our clients. She has SQL server 2005 installed on her machine as a named instance and GP 9.0. When she logs into her machine on the local machine account (not domain), she doesn't have any problems (also, all the software was installed under this account). When she logs into the domain account and tries to login to GP, she gets an error message: "Unhandled script exception: Wrong number of arguments passed to system 1339 from script 'SQL_Login' ...." The same ODBC source is used in both cases, but under the domain she get...

HELP!! #3
when I paste text I only see picture of a Help book on cursor! I had the same problem last months and I find my answer by uninstall my bluetooth device ,but now I haven't any bluethooth or the same.... please help me.. thanks We need more information. What kind of text are you trying to paste, a large amount of text into a whole spreadsheet, or a small amount of text into a single cell? How are you pasting? By selecting the text and clicking Edit->Copy, Edit->Paste, or are you using keyboard shortcuts, or are your using Paste Special? "toofan" wrote: > when I ...

error L3002
A couple questions. first where can I find a list of errors and their solutions? I am getting this error when using the MENU 'database',model,errorcheck error L3002: Child columns not specified. when I double click I get this: "This relationship is not shown because the entities it is connected to are not placed on the diagram." I am fairly new to using visio as a database diagramming tool. I do not know how to find the relationship if it is not on the diagram. How do I do this? thanks in advance. Kevin Ruggles Hi Kevin, > "This relati...

Excel VB
I have two cells in a workbook that are dates. The cell Start_Date has a valid date in it. The second cell Test_Date is either blank or has a valid date in it. The IsDate test on both cells works fine, however, on a blank cell the Month function extracts "12". Anyone know why? I had to put the month function within a test for IsDate to make it work. Seems like Month function on a blank cell should return something other than a 12. Dave Test code: If IsDate(Range("Start_Date")) Then 'Cell has 11/01/09 in it. MsgBox ("start date a d...

Formula help #13
E2 = A VALUE IN DOLLARS I2 = A NUMBER IN PERCENT need formula to make F2 = number that would make F2+(F2*I2)= E2 EXAMPLE E2=$11.99 I2=40% THEN F2= 8.56428571428571 using goal seek Thanks -- Carl Brehm Lake Lafourche Bird House Hebert, LA Keets, Tiels, GN Lories, Quakers Mitred Conures, TAG's, Bourkes Cages --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.581 / Virus Database: 368 - Release Date: 02/09/2004 F2 is =E2/(1+I2) you might want to round that -- Regards, Peo Sjoblom "Carl Brehm" <cr...

ZZ report error
I have a customer who does a Z report at the end of each shift and a ZZ report at the end of the day after his final Z report. When he runs the ZZ report it prints out the next Z report with all zeros. He is 100% sure that he is choosing the ZZ report option, but it seams to just print the next Z report which is all zeros since he just zeroed out. I have yet to see this in person but did see a copy of his report. Is there anything that would not allow him to print a ZZ report and just default to a Z report? Very strange. Thanks when you run a ZZ report it automatically prints a Z...

Pls help me out.
Greetings to all. i am a new Mac user. i inherited a ibook loaded with MsOffice for Mac that is in german. do you think i can reset the language to english without having to re-install the whole sofware? if so, how? thank you all so much! On 10/5/04 4:16 PM, in article 2ee7bd29.0410042316.72004f36@posting.google.com, "luc" <tchinlai@yahoo.com> wrote: > Greetings to all. > > i am a new Mac user. i inherited a ibook loaded with MsOffice for Mac > that is in german. > do you think i can reset the language to english without having to > re-install the whole s...

error message 0x800ccc0d
I keep getting this error message can anyone help please? Unable to send or receive messages for the pop3 account. The host 'pop3' could not be found. Please verify that you have entered the server name correctly. Server: 'pop3' Windows Live Mail Error ID: 0x800CCC0D Protocol: POP3 Port: 110 Secure(SSL): No Socket Error: 11001 All your settings are wrong. Are you trying to set up a Hotmail, MSN, or Live.com email account? If so, use the settings given = here: http://mailcall.spaces.live.com/Blog/cns!CC9301187A51FE33!49799.entry Use port 587 rather than p...

440 Login Timeout error after running DCPROMO
I just demoted my Exchange 2003 server as a DC and now when trying to access OWA, I am getting the 440 Login Timeout error.... ANy ideas? On Thu, 5 Apr 2007 13:38:05 -0700, Jason <Jason@discussions.microsoft.com> wrote: >I just demoted my Exchange 2003 server as a DC and now when trying to access >OWA, I am getting the 440 Login Timeout error.... > >ANy ideas? Call PSS. Running DCPromo on an Exchange Server is not supported. You can probably google around and find how to fix this, but I recommend calling PSS. What is PSS? "Andy David {MVP}" wrote: > ...

how to get rid of errors? #2
Thanks for the input, but actually I was more concerned about gettin rid of all error messages when the file loads up. It looks like yo gave me a cool utility, but I am not too concerned about fixing th links. Is there a way to ignore all messages that pop up when i load my exce sheet? regardless of what it is? Thank -- Jon ----------------------------------------------------------------------- Jonx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1317 View this thread: http://www.excelforum.com/showthread.php?threadid=26148 Think deleting all the "pha...

error creating entryid... while using exmerge...
Hi, we got exchange2k3 on a sbs2k3 and use exmerge to build .pst�s for backup... since a few days we get error messages for two new mailboxes and no pst�s from them...: [19:00:03] Error creating EntryID for '/O=TESLA/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=ZENTRALE' (GetMailboxInfoFromAB) [19:00:03] Error accessing directory object for '/O=TESLA/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=ZENTRALE'. Ignoring this mailbox. [19:00:03] Error creating EntryID for '/O=TESLA/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=BUG' (GetMailboxInfoFromAB) [19:00:03] Er...

Cannot Add any window to VB
Hello: I'm using a VM with GP 10.0 SP3 installed. In order to have the same as our production databases i copy the GP folder, entirely, which i think it was a mistake now I'm getting a "This cannot be added to Visual Basic", whenever i try to add a window, any window, to VB. Also it goes without saying that there's a lot of load errors starting GP. For the record the copy/paste thing worked, sort of, and before you start thinking that i was insane to have done this, there's a reason behind it, There's been a lot of tweaks to our production server, by our p...

vb from VC++
Hi, is it possible, and if so, to call a Vb macro from VC++. Or manipulate spreadsheets (ie draw graphs from the data within the workbooks) usinbg MFC functions cheers Anthony Presumably you are referring to MS Excel spreadsheets and workbooks. The easiest method would be to import the appropriate type library (.tlb) provided with Excel, having the class wizard generate the classes, which you then use to access the Excel Automation model (that is the same model that Excel VBA uses). Some investigation may be needed to determine which tlb to use, and to make sure you have a basic understand...

VB in Excel
In Excel I go into Visual Basic Editor, then Visual Basic Help, but when I type 'Passwords' for example it will bring up a list of related items, but some of them you can't select. Does this mean the installation CD is corrupt and hasn't installed all the components? I have reinstalled and repaired the installtion but to no avail. Any ideas anyone? Help! Thanks ...

Get the following error 'Publisher Unable to open File' in MS Pub
I'm currently working on this months magazine for my local sports association. I have saved the file shut down my PC restared and troed to open the file when i attempt this i get the following error message. Publisher unable to open file.. Can anyone help or know what is stopping me from opening this file i succesfully open other publisher files but cannot open this months edition.. I dont really fancy starting from stratch! -- Matt Owner MSE IT www.mse-it.co.uk Which version of Publisher are you using? You are welcome to send it to me and I'll see if I can open it. -- Jo...

Newbie needs help in programming in MFC for windows CE
See i want to create a program where i have a dialog box with two editboxes and a button called "Add" .My edit boxes should accept integer values and my 'Add' button must add those two values.I had already created a code where i can enter a string into an edit box and get it dispalyed through a message box in pocket pc 2003 .Now i tried declaring two integers and using GetDlgItemInt to read those integers from the editbox and get it displayed in a message box in the same way as above but cant get my code to work,am i wrong in doing so,pls pls help me out......i am using Evc++...

error value in axis
I am trying to plot a chart for varying number of weeks. I use the formula =IF(J63=0,NA(),J63-1) to stop with zero th week starting from the week I define. I also use similar formulas for getting data i.e =IF(ISERROR(VLOOKUP($K64,$A:$D,2)),NA(),VLOOKUP($K64,$A:$D,2)), where k64 indicates week number. The problem now is the data line stops with 'NA' values as expected, but the axis which contains week number shows 'NA' after the week numbers. Any ways to fix this. Now you need dynamic names (aka named ranges) that know where the numerical data ends, and use these names a...

Help with Input Mask
Hello, I'm a complete newbie trying to design a relatively simple data base for organizing choir music. I need to store arranger and composer names and be able to search for them. I will not need to track or display first and last names separately, so I would rather not use separate fields. But I need to have the data input consistently. Is there a way to create an input mask or validation rule to insure "Last name, First Name" format? Thank you, -- Leslie On Thu, 18 Oct 2007 13:12:00 -0700, leslie1165 wrote: > Hello, > > I'm a complete newbie trying t...

Outlook error #41
When I try to start Outlook, I get the following "The application failed to initialize properly (0xC0150002). Click on OK to terminate the application. How do I correct this? Hi Leon, please read this information from MS http://support.microsoft.com/default.aspx?scid=kb;EN-US;817117 or this one http://support.microsoft.com/default.aspx?scid=kb;EN-US;822520 -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "Leon" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag...

Errors 2448 and 3075!
I have a search comand button and it works most of the time, excpet with one name (so far) O'Shea. It comes up with the following error message: Run- time error '2448' You can't assign a value to this object. The debugger highlights this: Me.Filter = "[Surname] LIKE '*" & RetVal & "*'" However othertimes, it says this: Runt-time error '3075' Syntax error (missing operator) in query expression "[Surname] LIKE '*O'Shea*". Any ideas? Here's the whole section: Private Sub Command187_Click() Dim R...