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

How many sheets in the workbook?

"stewdizzle" wrote:

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

Also try Workbooks(ThisWorkbook).Close

"stewdizzle" wrote:

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

two worksheets one named budget and the other is named sheet1

On Jan 5, 10:23 pm, JLGWhiz wrote:
Also try Workbooks(ThisWorkbook).Close

"stewdizzle" wrote:
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

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


On Jan 6, 10:08 am, "stewdizzle" wrote:
two worksheets one named budget and the other is named sheet1

On Jan 5, 10:23 pm, JLGWhiz wrote:

Also try Workbooks(ThisWorkbook).Close

"stewdizzle" wrote:
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

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.

On Jan 6, 10:16 am, "stewdizzle" wrote:
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

On Jan 6, 10:08 am, "stewdizzle" wrote:

two worksheets one named budget and the other is named sheet1

On Jan 5, 10:23 pm, JLGWhiz wrote:

Also try Workbooks(ThisWorkbook).Close

"stewdizzle" wrote:
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

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"
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.

On Jan 6, 10:16 am, "stewdizzle" wrote:
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

On Jan 6, 10:08 am, "stewdizzle" wrote:

two worksheets one named budget and the other is named sheet1

On Jan 5, 10:23 pm, JLGWhiz wrote:

Also try Workbooks(ThisWorkbook).Close

"stewdizzle" wrote:
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