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