Run-time error 1004

  • Thread starter Thread starter Lee Jeffery
  • Start date Start date
L

Lee Jeffery

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
 
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
(e-mail address removed)
 
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
 
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
(e-mail address removed)
 
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
 
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
(e-mail address removed)
 
Back
Top