before close runtime error 9

[stewdizzle]

I am trying to get the code to delete a sheet, save, then close the
workbook. Right now it deletes the sheet, saves but then I get a Run
time Error "9" subscript out of range. I have the code below. Please
tell me what I am doing wrong.

Private Sub workbook_beforeclose(cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("sheet1").Delete
Application.DisplayAlerts = True
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

 

[Guest]

How many sheets in the workbook?

 

[Guest]

Also try Workbooks(ThisWorkbook).Close

 

[stewdizzle]

two worksheets one named budget and the other is named sheet1

 

[stewdizzle]

Using Workbooks(ThisWorkbook).Close gets rid of the error 9 but now i
get error 13 type mismatch. Everything works up until the close
command. Here is the code.

Private Sub workbook_beforeclose(cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("sheet1").Delete
Application.DisplayAlerts = True
ActiveWorkbook.Save
Workbooks(ThisWorkbook).Close
End Sub

 

[stewdizzle]

I'm not sure if this means anything but application.quit works fine.
for some reason i can't get it to just close the workbook.

 

[Jim Cone]

It appears that you have already told Excel to close the workbook
as you are using the beforeclose event.
So, try eliminating the last line of your code and see what happens.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"stewdizzle" <[email protected]>
wrote in message
I'm not sure if this means anything but application.quit works fine.
for some reason i can't get it to just close the workbook.

 

[stewdizzle]

Thanks for catching it. That did the trick.

It appears that you have already told Excel to close the workbook
as you are using the beforeclose event.
So, try eliminating the last line of your code and see what happens.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"stewdizzle" <[email protected]>
wrote in message
I'm not sure if this means anything but application.quit works fine.
for some reason i can't get it to just close the workbook.