deleteing peoblem

[Curt]

working in workbook useing this to create a sheet for mail merge in C:\Parade
Sheets("MailE").Select
Sheets("MailE").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Parade\MailE.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
When I try to delete with this
Sub Maildel()
Worksheets("C:\Parade\MailE.xls").Select
Worksheets("C:\Parade\MailE.xls").Delete
End Sub
I get error 9 sub script out of range
Seems if I can put the worksheet there I should be able to delete it know
the path is correct This is being done from C;\Parade\Parade.xls ? Thanks All

 

[OssieMac]

ActiveWorkbook.SaveAs Filename:= _
"C:\Parade\MailE.xls", ......
Above saves a workbook as a filename.

Workbooks are the files you save. Worksheets are the spreadsheets within the
workbook. I think that you have them mixed up.

The following refers to worksheets.

Worksheets("C:\Parade\MailE.xls").Select
Worksheets("C:\Parade\MailE.xls").Delete

If you are unsure of the syntax to complete the operation then try recording
a macro to do it. Note: You cannot delete a workbook while it is open.

Regards,

OssieMac

 

[OssieMac]

Sorry. Error in previous post. I don't think that you can record the workbook
delete process.

Regards,

OssieMac

 

[Curt]

The MailE I save is a worksheet from Parade.xls Is there a way to save this
so it may be deleted.
Thanks much for your responce

 

[OssieMac]

Hi Curt,

If I understand correctly, you have a workbook called ‘Parade.xls’ and in
that workbook you have a worksheet called ‘MailE’.

Your first macro copies the worksheet ‘MailE’ to a new workbook and you save
the new workbook as ‘MailE.xls’. this should work.

In the Sub Maildel() you want to delete the workbook ‘MailE’ and this is not
working.

First of all the workbook ‘MailE.xls’ must be closed before you can delete
it. The following code should work. However, if the workbook is not open you
will get an error trying to activate it. You can either delete the first 2
lines or you can use the second procedure if you are not sure if it is open.

Sub Maildel()

Windows("MailE.xls").Activate
ActiveWindow.Close

‘The following line deletes a workbook.
Kill "C:\Parade\Parade\MailE.xls"

End Sub

'Alternative to handle error if workbook not open

Sub Maildel_2()

On Error Resume Next
Windows("MailE.xlsm").Activate
ActiveWindow.Close
On Error GoTo 0

Kill "c:\Parade\Parade\MailE.xls"

End Sub

Regards,

OssieMac

 

[OssieMac]

Curt,

Replace that second macro with this.

Sub Maildel_2()

On Error Resume Next
Windows("MailE.xls").Activate
ActiveWindow.Close
On Error GoTo 0

Kill "c:\Parade\Parade\MailE.xls"

End Sub

I was working with xl2007 and forgot to amend the file extension from xlsm
to xls.

Regards,

OssieMac