Run-time error 1004

[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

 

[Nick Hodge]

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)

 

[Lee Jeffery]

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

 

[Nick Hodge]

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)

 

[Lee Jeffery]

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

 

[Nick Hodge]

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)