Run-time error 1004

I am using Excel 97 on NT and have a command button on a sheet to whic
I attached a macro for copying the sheet to a new workbook, deletin
specific columns and other unnecessary info, and saving the new book t
a network drive under a name with today's date for emailing to anothe
area.

When I perform the function manually everything works beautifully bu
when I attempt to execute the macro I get the following error
"Run-time error '1004': Copy method of worksheet class failed". when 
use the Debug button, VBA highlights Sheets("Daily").Copy as th
culprit. Macro follows:

Private Sub CommandButton2_Click()
Sheets("Daily").Select
Sheets("Daily").Copy
ActiveWorkbook.Activate
Columns("H:J").Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.Shapes("CommandButton2").Select
Selection.Delete
ActiveSheet.Shapes("CommandButton1").Select
Selection.Delete
Range("B4").Select
ActiveWorkbook.SaveAs FileName:= _
"G:\ER\ECM-POL Commencements\POL Commencements_05-07-2004.xls"
FileFormat:= _
xlNormal, Password:="", WriteResPassword:=""
ReadOnlyRecommended:=True, _
CreateBackup:=True
ActiveWorkbook.Close
End Sub

The code is a little heavier than my original macro as I tried 
different approach to get this going but have not succeeded.

I had this working fine on my home PC using Excel 97 on XP so I don'
understand why this has decided to spit the dummy now.

Can anyone help shed some light on this, please?:eek

--
Message posted from http://www.ExcelForum.com

0
7/6/2004 7:36:06 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
543 Views

Similar Articles

[PageSpeed] 21

Lee

Just a long shot by try going into the properties of your commandbutton and
setting the TakeFocusOnClick property to False

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Lee Jeffery >" <<Lee.Jeffery.18you4@excelforum-nospam.com> wrote in message
news:Lee.Jeffery.18you4@excelforum-nospam.com...
> I am using Excel 97 on NT and have a command button on a sheet to which
> I attached a macro for copying the sheet to a new workbook, deleting
> specific columns and other unnecessary info, and saving the new book to
> a network drive under a name with today's date for emailing to another
> area.
>
> When I perform the function manually everything works beautifully but
> when I attempt to execute the macro I get the following error:
> "Run-time error '1004': Copy method of worksheet class failed". when I
> use the Debug button, VBA highlights Sheets("Daily").Copy as the
> culprit. Macro follows:
>
> Private Sub CommandButton2_Click()
> Sheets("Daily").Select
> Sheets("Daily").Copy
> ActiveWorkbook.Activate
> Columns("H:J").Select
> Selection.Delete Shift:=xlToLeft
> ActiveSheet.Shapes("CommandButton2").Select
> Selection.Delete
> ActiveSheet.Shapes("CommandButton1").Select
> Selection.Delete
> Range("B4").Select
> ActiveWorkbook.SaveAs FileName:= _
> "G:\ER\ECM-POL Commencements\POL Commencements_05-07-2004.xls",
> FileFormat:= _
> xlNormal, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:=True, _
> CreateBackup:=True
> ActiveWorkbook.Close
> End Sub
>
> The code is a little heavier than my original macro as I tried a
> different approach to get this going but have not succeeded.
>
> I had this working fine on my home PC using Excel 97 on XP so I don't
> understand why this has decided to spit the dummy now.
>
> Can anyone help shed some light on this, please?:eek:
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
7/6/2004 7:55:00 PM
Thanks, Nick.

I did this and progressed past Sheets("Daily").Copy but now bomb out o
Columns("H:J").Select. I now get Run-time error "1004": Select metho
of Range class failed.

Any further thoughts, please? This is driving me to coffee!

Any suggestions would be very welcome

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 12:48:27 AM
Lee

The recorded code is ugly with all the activations and selects which are not
necessary really, I've tidied it up a little, presuming the CommandButtons
are on the worksheet you are copying to another book and then deleting them.
Note: very little selecting, apart from where I thought it may be you
wanting to end with a certain cell selected.

Private Sub CommandButton2_Click()
Sheets("Daily").Copy
With ActiveSheet
    .Columns("H:J").Delete Shift:=xlToLeft
    .Shapes("CommandButton2").Delete
    .Shapes("CommandButton1").Delete
End With
ActiveSheet.Range("B4").Select
With ActiveWorkbook
    .SaveAs Filename:="G:\ER\ECM-POL Commencements\POL
Commencements_05-07-2004.xls"
    .Close
End With
End Sub

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Lee Jeffery >" <<Lee.Jeffery.1900mp@excelforum-nospam.com> wrote in message
news:Lee.Jeffery.1900mp@excelforum-nospam.com...
> Thanks, Nick.
>
> I did this and progressed past Sheets("Daily").Copy but now bomb out on
> Columns("H:J").Select. I now get Run-time error "1004": Select method
> of Range class failed.
>
> Any further thoughts, please? This is driving me to coffee!
>
> Any suggestions would be very welcome.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
7/7/2004 8:49:26 PM
Nick,

You are a wonderful person! This works beautifully.  I'll be able t
get some sleep now I don't have to drink extra coffee!!

Your suggestion has just helped me finalise a huge productivit
improvement and I am very grateful for your assistance.

Thanks again.

P.S. I've enrolled in an Excel/VBA class so I can learn a lot mor
about the correct way to go about producing code for the fairly basi
things I need to do with Excel

--
Message posted from http://www.ExcelForum.com

0
7/8/2004 1:29:30 AM
Lee

Pleasure.

Stick around here too.  It's amazing what you pick up which spurs you on to
the next project

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Lee Jeffery >" <<Lee.Jeffery.191x74@excelforum-nospam.com> wrote in message
news:Lee.Jeffery.191x74@excelforum-nospam.com...
> Nick,
>
> You are a wonderful person! This works beautifully.  I'll be able to
> get some sleep now I don't have to drink extra coffee!!
>
> Your suggestion has just helped me finalise a huge productivity
> improvement and I am very grateful for your assistance.
>
> Thanks again.
>
> P.S. I've enrolled in an Excel/VBA class so I can learn a lot more
> about the correct way to go about producing code for the fairly basic
> things I need to do with Excel.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
7/8/2004 6:14:36 AM
Reply:

Similar Artilces:

count text through worksheets generates error
Hi all, In a workbook with a lot of worksheets i want to count the number of times a specific text value occurs in a cell. All sheets have the same layout. So, for instance, when i want to count the number of times the value "yes" occurs in cell B2 through sheets 1 to 5 and i use the function =countif(sheet1:sheet5!b2,"yes") excel generates an error message (#value!). I don't understand why, because if i would use the function in 1 sheet only I get the required result. I suspect that some functions cannot work on grouped sheets. Is there a list somewhere that e...

Combo Box Search Time
Hello Group, I have a combo box that I currently use with the Auto Expand feature on. This combo box points to a field in a query with 400,000 records. The box works fine but has about a 30 second latency delay from the time it gets focus to the time where it will display the records. I would like to cut this latency down to as little as possible. As a side note, I have 2 forms each with a combo box pointing to 2 different queries each with 400K records. The first combo box is a zip code lookup and works super fast; the moment you click it the results pop right up. The second combo box ...

DPM connection error
Hi, I just applied SP1 to my DPM 2007 server. Everything seemed good to go until I started to update the client agents. After about 10 minutes the DPM server became very unresponsive. I rebooted it and now when I try to connect to the Admin console I get an error 'Unable to cnnect to dpm.domain.com (ID: 948) Verify that the DPM service is running on this computer". I have gone through all of the services and set them to start automatically and rebooted once more and I still receive the error. Any help would be awesome. Thanks! Check the status of the DPM servic...

Can someone help with this error
I am randomly getting an access error in my application and I am trying to find it. The error seems to occur when allocating a buffer in the CString code. Does anyone know if the dump below is pointing to anything specific? It seems like the error is occuring when 'text' is be allocated but I don't know why. // this is the function call in the ArchExists(..) module where the CString is allocated (parm1). CComSetup::ExtractParams( text, &type1, &desc1, &name1, &id1 ); // This is the declaration of the function void CComSetup::ExtractParams( CString src, CStrin...

Outlook 2000 memory error
Hi, When I deleted an item in Calendar, I got error message that "There is not enough memory available to perform the operation." I am using Outlook 2000. Thanks in advance. Grace ...

need help... Getting a error when I add a xml schema include location xsd file
Hi, I am trying to add the following line to my xml schema xsd file <xsd:include schemaLocation="../../../Common/datatypes.xsd"/> Here is my code XmlSchemaInclude include = new XmlSchemaInclude(); include.SchemaLocation = "../../../Common/datatypes.xsd"; schema.Items.Add(include); when I print out the schema, it crashes StreamWriter sw = new StreamWriter(@strFormName+".xsd", false, Encoding.UTF8); schema.Write(sw); sw.Close(); Any help is greatly appreciated, Thanks ...

Global error handler
Is there, in ACCCESS 2007, and/or VB a global error handler? I've checked the docs but not sure on the feasibility of this. I've seen On Error and OnError, and a discussion excepotion handling, but again, I'm not sure on the implementation of error and exception handling. -- The personal opinion of Gary G. Little You must add the error handling to each routine. There's not a way to just make it automatically go to an exception handler without doing so. In each routine, you can call a generic error handler. Here's an example of how: Error Handling in VBA at: ...

error with the form when using asp.
I get this error, when i submit the form. The error comes up and some technical advisor from my host company told me to change it to ASP Email but i don't understand what he was talking about. Can someone provide assistance. Server object error 'ASP 0178 : 80070005' Server.CreateObject Access Error /WebAppSub.asp, line 9 The call to Server.CreateObject failed while checking permissions. Access is denied to this object. ...

GP Client install errors
I installed GP 8.0 from a installation template on 1 XP machine with no problems. I have the ODBC properly setup, and bcp.exe properly installed to satisfy GP Utilities. I am able to log in with different users, no problem. I did the same thing on two other XP machines and both machines give this Dexterity Runtime error on start-up. Unable to open dictionary :C:PROGRA~1/MID1AC~1/GREATP~1/dictionary name.DIC. I get this error for the following .dic's: ADVSECURE.DIC ADV_FRM.DIC ADV_RPT.DIC AREC.DIC ARECFRM.DIC ARECRPT.DIC CFM.DIC CFMFORM.DIC CMFRPTS.DIC EXP1493.DIC EXP1493F.DIC EXP1493R....

return a zero for error
I'm having a problem with a spreadsheet I'm developing which records average marks of groups of students over several weeks. I want to display a "Running Average" of those averages as I enter the data, however, because I do this week by week there are inevitably weeks which have not had any data entered and her I see the error #DIV/0! for the formula =AVERAGE(IF(R5:R20<>0, R5:R20,"")). I do understand why I'm getting this (because I'm dividing by zero) but would like to return a zero so that the average of the averages will return a number rather tha...

Money Investment Toolbar Error
All of a sudden, whenever I try to access my portfolios in the Deluxe version, I get this error: AppName: iexplore.exe, AppVer: 6.0.52800.1106, ModName: myisam05.dll, ModVer: 4.0.3107.50512, Offset: 00006e27. I can remove the Toolbar and use the basic portfolio version but continue to get the above error and an IE restart whenever I try to upgrade to Deluxe. Any suggestions> In microsoft.public.money, Dencoden wrote: >All of a sudden, whenever I try to access my portfolios in the Deluxe >version, I get this error: AppName: iexplore.exe, AppVer: 6.0.52800.1106, >ModName...

Macro Run on WorkBook Open?
Have a macro that I want to run when the file is first opened. Is this possible? If so, How? Thanks in advance -- / Sean the Mc / "I have not failed. I've just found 10,000 ways that won't work." - Thomas Alva Edison (1847-1931) You answered your own question. Right click on the excel icon just to the left of FILE> the workbook_open event is there. -- Don Guillett SalesAid Software donaldb@281.com "What-a-Tool" <Die!FrigginSpammersDieDie!@IHateSpam.Net> wrote in message news:MetMc.7592$BX.2445@lakeread08... > Have a macro that I want to run w...

Time formats in Excel 2003
Being in the UK, I obviously want to use the English(UK) date format, so Christmas Day is 25/12/05 rather than 12/25/05. However, this defaults the time format to hh/mm/ss (why, I do not know, since I can assure American readers we're not normally that precise), so I have to reset it English(US) to get hh/mm, which resets the default date format to mm/dd/yy, so I have to change it back the next time I want to format a cell as a date. Any way round this? It's only a minor problem but it's irritating me. Steve Stephen, If you set up your own custom format with hh/mm, it w...

Only run if check box is checked
I have some code that I only want to be run if a check box is checked. So I inserted an ActiveX check box which I have named cbRecord. I then put the following IF around the code which is initiated by clicking a spinner form control: If cbRecord.Value Then .... .... .... End If However, when the code is run I get the following error: Run time error '424': Object required. When I go to debug, the code doesn't even know that my check box exists. TIA...Geoff Try If Sheets("Sheet1").CheckBoxes("Check Box 1").Value = xlOn Then '...

WorkFlow and Time
Question is will this work. Just started playing with the time assebly in 3.0. Attempting to notify someone 12 months before a date field. Example: Date field is populated as 12/1/2007 send an email on 12/1/2006. Will an If then work with the subtract time function? Will just setting an action to take place using the subtract time function work? Thoughts? ...

Authentication Error 04-28-06
One of our users is suddenly getting Aucthentication Error. "Microsfot CRM could not log you on to the system. Make sure your user record is eanbled and that you have been assigned at least one security role" I have checked the user in CRM, enabled and also has a role assigned. Checked the SQL databases which has relevant records. Checked the Deployment manager. So everything is normal as expected but for some reason the message started today. The other users are fine. This use has been working in CRM up until today. Thanks Nilesh ...

Explorer.EXE
When I switch on my computer, the following message widnow showup. I can not do anything, except to the the task manager. I try to restart the computer and the problem is gone. ===================================================== Explorer.EXE - Application Error The application failed to initialize properly (0xc0000142). Click on ok to terminate the application. ===================================================== The problem occur very frequently and I can only solve it by restart the computer. Sometimes I need to restart several time. Is anything wrong in my compu...

Project Time in Business Portal
Is anyone using this? We're trying to configure BP3.0 to allow Project Timesheet entry by admins on behalf of department employees. It's easy to set the user proxies in regular timesheet entry in BP but I can't find where to set it in Project Timesheet. Anyone have experience with this? Thx, Frank Hamelly ...

Compile error in Excel 2002
I have Excel 2002 installed on a Windows 7 machine. While this version of Excel was installed on Win XP, it worked fine. However, since I installed it on Win 7, every time I open or close an Excel file, I'm getting an MS Visual Basic compile error in hidden module. On opening a file, it is AutoExecNew module, on exit - DistMon. I followed advice in the MS KB Article ID: 307410 and removed both pdfmaker.dot and pdfmaker.xla. But it did not help. The error message pops up again... Is there any other solution to this problem? -- Ilya Zeldes Fort Myers, Florida Most ...

Can I force confirmation prompt on close? Or tell Outlook to run in background?
I am constantly accidentally pressing the close button instead of "minimize" when I am at work. I press close explicitly but I would like behavior like antivirus or IM clients. I want Outlook to continue to run in the background, so it can continue to check for email, alarms, etc. Is there either a way to force confirmation of closure, or to have an outlook background task set up which doesn't close so easily? Rufus Rufus V. Smith <nospam@nospam.com> wrote: > I am constantly accidentally pressing the close > button instead of "minimize" when I am at ...

OWA 2007 HTTP 403 ERROR
Hello All, I’ve been able to successfully get up and running in a virtual environment (yes, yes I know it isn’t supported…but it’s a pseudo lab environment) exchange 2007. Up until two days ago the setup has been running very well allowing me to access my mail via OWA, as well as with the “Outlook Anywhere” components . The problem was that I couldn’t access my mail using the “Anywhere” components. In investigating this I found that the following events were logged. W3SVC Event ID 1039 and W3SVC Event ID 1002 , it appears as though I resolved both issues by running the command outlin...

Catastrophic Error in XL 2003
I've written some pretty standard, but complicated, VBA code under XL2000. A user with XL2003 gets a "catastrophic error" when opening the file (macros enabled), which sends him to the VBA debugger - but in a loop. Need Task Mgr to quit the application. This is NOT a shared workbook - I read those posts but haven't found a clue as to what could be causing this. I also got this error on an XL2003 machine (same description as above). After 'resolving' it by saving as an older version of Excel (even though it originally was XL2000!), I started getting "Out of m...

IE script error in Outlook today
I've been recently getting an IE script error: Line: 325 Char: 1 Error: access denied Code: 0 Url: Outlook today Happens 2-3 times a week, in and out of outlook today page several times a day. Outlook 2003, latest version of IE and patched. Ran repair of office, reinstalled IE. Anything else? Hi Mark, RESOLUTION To Repair Outlook 2000 To run the Detect and Repair feature in your Office programs, follow these steps: 1.) Start an Office 2003 program. 2.) On the Help menu, click Detect and Repair. 3.) In the Detect and Repair dialog box, do any of the following: -Click to select th...

SOP_Document_TEMP has an incorrect record Length error
In Great Plains 8.0 Our Sales Transaction Entry, Print Invoice option throws an error occasionaly that I haven't been able to track down. The error is: 'An open operation on table 'SOP_Document_TEMP' has an incorrect record length'. If they repeate the invoice print option it works fine after 2 or 3 trys. My Sales Transaction Entry form and the Invoice are modified. I'm not able to repeate the error so it's difficult to track down. Any suggestions? Thanks Kevin R. Kevin The SOP_Document_TEMP table is a ctree temporary table which is created in the l...

Run time error 2465
I'm trying to open an access db and upon loading or opening I get Run Time Error 2465 Can not find field 'cmdLock' referred to in your expression. When I go to Debug I see it highlight the line in question: frm!cmdLock.Caption = IIf(bLock, "Un&lock", "&Lock") It seems OK, what am I missing? One other strange thing, (well strange to me) is that this db is located on a network drive. No one else has this problem, but me? Any ideas? Hi Rock, When an application works on other PCs, but fails on one PC, this is usually a sign of a MISSING reference...