Hi,
I'm trying to delete two sheets from a workbook that were added during
a Workbook_Open event. In the close procedure I have the following
code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
DoEvents
On Error Resume Next
If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
ActiveWorkbook.Worksheets("Summary").Visible = True
ActiveWorkbook.Worksheets("Summary").Select
ActiveWorkbook.Worksheets("Summary").Delete
End If

On Error GoTo 0

Worksheets(Sheets.Count).Visible = True
Worksheets(Sheets.Count).Select
Worksheets(Sheets.Count).Delete

Application.Caption = Empty

ThisWorkbook.Save
ThisWorkbook.Close

Application.DisplayAlerts = True
Application.Quit
End Sub

The problem is that when I open the workbook, I get a '1004' error
because the summary sheet already exists. I put a counter in the
Workbook_Open procedure to verify that it was only firing once and
message box at the tail end of the Workbook_BeforeClose procedure
asking for the last sheet name, which was correct. So, why can't I
delete these two sheets?
Thanks.
James

Try this. No need to unhide or select or check or close if quitting
excel.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveWorkbook
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("Summary").Delete
.Worksheets(Sheets.Count).Delete
.Save
End With
Application.Quit
End Sub



On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
> Hi,
> I'm trying to delete two sheets from a workbook that were added during
> a Workbook_Open event. In the close procedure I have the following
> code:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Application.DisplayAlerts = False
> DoEvents
> On Error Resume Next
> If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> * * ActiveWorkbook.Worksheets("Summary").Visible = True
> * * ActiveWorkbook.Worksheets("Summary").Select
> * * ActiveWorkbook.Worksheets("Summary").Delete
> End If
>
> On Error GoTo 0
>
> Worksheets(Sheets.Count).Visible = True
> Worksheets(Sheets.Count).Select
> Worksheets(Sheets.Count).Delete
>
> Application.Caption = Empty
>
> ThisWorkbook.Save
> ThisWorkbook.Close
>
> Application.DisplayAlerts = True
> Application.Quit
> End Sub
>
> The problem is that when I open the workbook, I get a '1004' error
> because the summary sheet already exists. I put a counter in the
> Workbook_Open procedure to verify that it was only firing once and
> message box at the tail end of the Workbook_BeforeClose procedure
> asking for the last sheet name, which was correct. So, why can't I
> delete these two sheets?
> Thanks.
> James

On Feb 3, 10:42*am, Don Guillett <dguille...@gmail.com> wrote:
> Try this. No need to unhide or select or check or close if quitting
> excel.
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> With ActiveWorkbook
> Application.DisplayAlerts = False
> On Error Resume Next
> * .Sheets("Summary").Delete
> * .Worksheets(Sheets.Count).Delete
> * .Save
> End With
> Application.Quit
> End Sub
>
> On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
>
>
>
> > Hi,
> > I'm trying to delete two sheets from a workbook that were added during
> > a Workbook_Open event. In the close procedure I have the following
> > code:
>
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Application.DisplayAlerts = False
> > DoEvents
> > On Error Resume Next
> > If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> > * * ActiveWorkbook.Worksheets("Summary").Visible = True
> > * * ActiveWorkbook.Worksheets("Summary").Select
> > * * ActiveWorkbook.Worksheets("Summary").Delete
> > End If
>
> > On Error GoTo 0
>
> > Worksheets(Sheets.Count).Visible = True
> > Worksheets(Sheets.Count).Select
> > Worksheets(Sheets.Count).Delete
>
> > Application.Caption = Empty
>
> > ThisWorkbook.Save
> > ThisWorkbook.Close
>
> > Application.DisplayAlerts = True
> > Application.Quit
> > End Sub
>
> > The problem is that when I open the workbook, I get a '1004' error
> > because the summary sheet already exists. I put a counter in the
> > Workbook_Open procedure to verify that it was only firing once and
> > message box at the tail end of the Workbook_BeforeClose procedure
> > asking for the last sheet name, which was correct. So, why can't I
> > delete these two sheets?
> > Thanks.
> > James- Hide quoted text -
>
> - Show quoted text -

Thanks, Don, unfortunately that didn't work, so I assume that the
problem really is in the Workbook_Open procedure, even though my
counter only said it ran once. Here is the code that I use to add the
two sheets:

Private Sub Workbook_Open()
Application.EnableEvents = True

With ThisWorkbook.Worksheets
.Add After:=Worksheets(Sheets.Count)
Worksheets(2).Range("E2:S2").Copy
Destination:=Worksheets(Sheets.Count).Cells(1, 1)
Worksheets(Sheets.Count).Visible = False
End With
Application.CutCopyMode = False
With ThisWorkbook.Worksheets
.Add After:=Worksheets(Sheets.Count)
Worksheets(Sheets.Count).Visible = False
End With
Worksheets(Sheets.Count).Name = "Summary"
End Sub

This code tested fine in xl2003 to add the two sheets on open and
delete on close. If you do not want the sheets created when you open
the assign to a button/shape. Or, send file to me at dguillett1
@gmail.com

Private Sub Workbook_Open()
ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
Worksheets(2).Range("E2:S2").Copy Cells(1, 1)
ActiveSheet.Visible = False

ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
ActiveSheet.Name = "Summary"
ActiveSheet.Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveWorkbook
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("Summary").Delete
.Worksheets(Sheets.Count).Delete
.Save
End With
Application.Quit
End Sub





On Feb 3, 11:04*am, John Smith <jamasm2...@gmail.com> wrote:
> On Feb 3, 10:42*am, Don Guillett <dguille...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > Try this. No need to unhide or select or check or close if quitting
> > excel.
>
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > With ActiveWorkbook
> > Application.DisplayAlerts = False
> > On Error Resume Next
> > * .Sheets("Summary").Delete
> > * .Worksheets(Sheets.Count).Delete
> > * .Save
> > End With
> > Application.Quit
> > End Sub
>
> > On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > Hi,
> > > I'm trying to delete two sheets from a workbook that were added during
> > > a Workbook_Open event. In the close procedure I have the following
> > > code:
>
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > Application.DisplayAlerts = False
> > > DoEvents
> > > On Error Resume Next
> > > If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> > > * * ActiveWorkbook.Worksheets("Summary").Visible = True
> > > * * ActiveWorkbook.Worksheets("Summary").Select
> > > * * ActiveWorkbook.Worksheets("Summary").Delete
> > > End If
>
> > > On Error GoTo 0
>
> > > Worksheets(Sheets.Count).Visible = True
> > > Worksheets(Sheets.Count).Select
> > > Worksheets(Sheets.Count).Delete
>
> > > Application.Caption = Empty
>
> > > ThisWorkbook.Save
> > > ThisWorkbook.Close
>
> > > Application.DisplayAlerts = True
> > > Application.Quit
> > > End Sub
>
> > > The problem is that when I open the workbook, I get a '1004' error
> > > because the summary sheet already exists. I put a counter in the
> > > Workbook_Open procedure to verify that it was only firing once and
> > > message box at the tail end of the Workbook_BeforeClose procedure
> > > asking for the last sheet name, which was correct. So, why can't I
> > > delete these two sheets?
> > > Thanks.
> > > James- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks, Don, unfortunately that didn't work, so I assume that the
> problem really is in the Workbook_Open procedure, even though my
> counter only said it ran once. Here is the code that I use to add the
> two sheets:
>
> Private Sub Workbook_Open()
> Application.EnableEvents = True
>
> With ThisWorkbook.Worksheets
> * * .Add After:=Worksheets(Sheets.Count)
> * * Worksheets(2).Range("E2:S2").Copy
> Destination:=Worksheets(Sheets.Count).Cells(1, 1)
> * * Worksheets(Sheets.Count).Visible = False
> End With
> Application.CutCopyMode = False
> With ThisWorkbook.Worksheets
> * * .Add After:=Worksheets(Sheets.Count)
> * * Worksheets(Sheets.Count).Visible = False
> End With
> Worksheets(Sheets.Count).Name = "Summary"
> End Sub

On Feb 3, 11:49*am, Don Guillett <dguille...@gmail.com> wrote:
> This code tested fine in xl2003 to add the two sheets on open and
> delete on close. If you do not want the sheets created when you open
> the assign to a button/shape. Or, send file to me at dguillett1
> @gmail.com
>
> Private Sub Workbook_Open()
> ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> Worksheets(2).Range("E2:S2").Copy Cells(1, 1)
> ActiveSheet.Visible = False
>
> ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> ActiveSheet.Name = "Summary"
> ActiveSheet.Visible = False
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> With ActiveWorkbook
> Application.DisplayAlerts = False
> On Error Resume Next
> * .Sheets("Summary").Delete
> * .Worksheets(Sheets.Count).Delete
> * .Save
> End With
> Application.Quit
> End Sub
>
> On Feb 3, 11:04*am, John Smith <jamasm2...@gmail.com> wrote:
>
>
>
> > On Feb 3, 10:42*am, Don Guillett <dguille...@gmail.com> wrote:
>
> > > Try this. No need to unhide or select or check or close if quitting
> > > excel.
>
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > With ActiveWorkbook
> > > Application.DisplayAlerts = False
> > > On Error Resume Next
> > > * .Sheets("Summary").Delete
> > > * .Worksheets(Sheets.Count).Delete
> > > * .Save
> > > End With
> > > Application.Quit
> > > End Sub
>
> > > On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > > Hi,
> > > > I'm trying to delete two sheets from a workbook that were added during
> > > > a Workbook_Open event. In the close procedure I have the following
> > > > code:
>
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > Application.DisplayAlerts = False
> > > > DoEvents
> > > > On Error Resume Next
> > > > If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> > > > * * ActiveWorkbook.Worksheets("Summary").Visible = True
> > > > * * ActiveWorkbook.Worksheets("Summary").Select
> > > > * * ActiveWorkbook.Worksheets("Summary").Delete
> > > > End If
>
> > > > On Error GoTo 0
>
> > > > Worksheets(Sheets.Count).Visible = True
> > > > Worksheets(Sheets.Count).Select
> > > > Worksheets(Sheets.Count).Delete
>
> > > > Application.Caption = Empty
>
> > > > ThisWorkbook.Save
> > > > ThisWorkbook.Close
>
> > > > Application.DisplayAlerts = True
> > > > Application.Quit
> > > > End Sub
>
> > > > The problem is that when I open the workbook, I get a '1004' error
> > > > because the summary sheet already exists. I put a counter in the
> > > > Workbook_Open procedure to verify that it was only firing once and
> > > > message box at the tail end of the Workbook_BeforeClose procedure
> > > > asking for the last sheet name, which was correct. So, why can't I
> > > > delete these two sheets?
> > > > Thanks.
> > > > James- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks, Don, unfortunately that didn't work, so I assume that the
> > problem really is in the Workbook_Open procedure, even though my
> > counter only said it ran once. Here is the code that I use to add the
> > two sheets:
>
> > Private Sub Workbook_Open()
> > Application.EnableEvents = True
>
> > With ThisWorkbook.Worksheets
> > * * .Add After:=Worksheets(Sheets.Count)
> > * * Worksheets(2).Range("E2:S2").Copy
> > Destination:=Worksheets(Sheets.Count).Cells(1, 1)
> > * * Worksheets(Sheets.Count).Visible = False
> > End With
> > Application.CutCopyMode = False
> > With ThisWorkbook.Worksheets
> > * * .Add After:=Worksheets(Sheets.Count)
> > * * Worksheets(Sheets.Count).Visible = False
> > End With
> > Worksheets(Sheets.Count).Name = "Summary"
> > End Sub- Hide quoted text -
>
> - Show quoted text -

I can send you the file, but it's over 5.5MB. Is that okay?

On Feb 3, 11:49*am, Don Guillett <dguille...@gmail.com> wrote:
> This code tested fine in xl2003 to add the two sheets on open and
> delete on close. If you do not want the sheets created when you open
> the assign to a button/shape. Or, send file to me at dguillett1
> @gmail.com
>
> Private Sub Workbook_Open()
> ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> Worksheets(2).Range("E2:S2").Copy Cells(1, 1)
> ActiveSheet.Visible = False
>
> ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> ActiveSheet.Name = "Summary"
> ActiveSheet.Visible = False
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> With ActiveWorkbook
> Application.DisplayAlerts = False
> On Error Resume Next
> * .Sheets("Summary").Delete
> * .Worksheets(Sheets.Count).Delete
> * .Save
> End With
> Application.Quit
> End Sub
>
> On Feb 3, 11:04*am, John Smith <jamasm2...@gmail.com> wrote:
>
>
>
> > On Feb 3, 10:42*am, Don Guillett <dguille...@gmail.com> wrote:
>
> > > Try this. No need to unhide or select or check or close if quitting
> > > excel.
>
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > With ActiveWorkbook
> > > Application.DisplayAlerts = False
> > > On Error Resume Next
> > > * .Sheets("Summary").Delete
> > > * .Worksheets(Sheets.Count).Delete
> > > * .Save
> > > End With
> > > Application.Quit
> > > End Sub
>
> > > On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > > Hi,
> > > > I'm trying to delete two sheets from a workbook that were added during
> > > > a Workbook_Open event. In the close procedure I have the following
> > > > code:
>
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > Application.DisplayAlerts = False
> > > > DoEvents
> > > > On Error Resume Next
> > > > If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> > > > * * ActiveWorkbook.Worksheets("Summary").Visible = True
> > > > * * ActiveWorkbook.Worksheets("Summary").Select
> > > > * * ActiveWorkbook.Worksheets("Summary").Delete
> > > > End If
>
> > > > On Error GoTo 0
>
> > > > Worksheets(Sheets.Count).Visible = True
> > > > Worksheets(Sheets.Count).Select
> > > > Worksheets(Sheets.Count).Delete
>
> > > > Application.Caption = Empty
>
> > > > ThisWorkbook.Save
> > > > ThisWorkbook.Close
>
> > > > Application.DisplayAlerts = True
> > > > Application.Quit
> > > > End Sub
>
> > > > The problem is that when I open the workbook, I get a '1004' error
> > > > because the summary sheet already exists. I put a counter in the
> > > > Workbook_Open procedure to verify that it was only firing once and
> > > > message box at the tail end of the Workbook_BeforeClose procedure
> > > > asking for the last sheet name, which was correct. So, why can't I
> > > > delete these two sheets?
> > > > Thanks.
> > > > James- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks, Don, unfortunately that didn't work, so I assume that the
> > problem really is in the Workbook_Open procedure, even though my
> > counter only said it ran once. Here is the code that I use to add the
> > two sheets:
>
> > Private Sub Workbook_Open()
> > Application.EnableEvents = True
>
> > With ThisWorkbook.Worksheets
> > * * .Add After:=Worksheets(Sheets.Count)
> > * * Worksheets(2).Range("E2:S2").Copy
> > Destination:=Worksheets(Sheets.Count).Cells(1, 1)
> > * * Worksheets(Sheets.Count).Visible = False
> > End With
> > Application.CutCopyMode = False
> > With ThisWorkbook.Worksheets
> > * * .Add After:=Worksheets(Sheets.Count)
> > * * Worksheets(Sheets.Count).Visible = False
> > End With
> > Worksheets(Sheets.Count).Name = "Summary"
> > End Sub- Hide quoted text -
>
> - Show quoted text -

Don, I finally got this to work! I had to manually delete the sheets
before the code would delete the sheets - even though the
workbook_open procedure created them. Thank you for your help, because
I really appreciate the time and effort that everyone gives me.
James

On Feb 3, 11:49*am, Don Guillett <dguille...@gmail.com> wrote:
> This code tested fine in xl2003 to add the two sheets on open and
> delete on close. If you do not want the sheets created when you open
> the assign to a button/shape. Or, send file to me at dguillett1
> @gmail.com
>
> Private Sub Workbook_Open()
> ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> Worksheets(2).Range("E2:S2").Copy Cells(1, 1)
> ActiveSheet.Visible = False
>
> ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> ActiveSheet.Name = "Summary"
> ActiveSheet.Visible = False
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> With ActiveWorkbook
> Application.DisplayAlerts = False
> On Error Resume Next
> * .Sheets("Summary").Delete
> * .Worksheets(Sheets.Count).Delete
> * .Save
> End With
> Application.Quit
> End Sub
>
> On Feb 3, 11:04*am, John Smith <jamasm2...@gmail.com> wrote:
>
>
>
> > On Feb 3, 10:42*am, Don Guillett <dguille...@gmail.com> wrote:
>
> > > Try this. No need to unhide or select or check or close if quitting
> > > excel.
>
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > With ActiveWorkbook
> > > Application.DisplayAlerts = False
> > > On Error Resume Next
> > > * .Sheets("Summary").Delete
> > > * .Worksheets(Sheets.Count).Delete
> > > * .Save
> > > End With
> > > Application.Quit
> > > End Sub
>
> > > On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > > Hi,
> > > > I'm trying to delete two sheets from a workbook that were added during
> > > > a Workbook_Open event. In the close procedure I have the following
> > > > code:
>
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > Application.DisplayAlerts = False
> > > > DoEvents
> > > > On Error Resume Next
> > > > If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> > > > * * ActiveWorkbook.Worksheets("Summary").Visible = True
> > > > * * ActiveWorkbook.Worksheets("Summary").Select
> > > > * * ActiveWorkbook.Worksheets("Summary").Delete
> > > > End If
>
> > > > On Error GoTo 0
>
> > > > Worksheets(Sheets.Count).Visible = True
> > > > Worksheets(Sheets.Count).Select
> > > > Worksheets(Sheets.Count).Delete
>
> > > > Application.Caption = Empty
>
> > > > ThisWorkbook.Save
> > > > ThisWorkbook.Close
>
> > > > Application.DisplayAlerts = True
> > > > Application.Quit
> > > > End Sub
>
> > > > The problem is that when I open the workbook, I get a '1004' error
> > > > because the summary sheet already exists. I put a counter in the
> > > > Workbook_Open procedure to verify that it was only firing once and
> > > > message box at the tail end of the Workbook_BeforeClose procedure
> > > > asking for the last sheet name, which was correct. So, why can't I
> > > > delete these two sheets?
> > > > Thanks.
> > > > James- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks, Don, unfortunately that didn't work, so I assume that the
> > problem really is in the Workbook_Open procedure, even though my
> > counter only said it ran once. Here is the code that I use to add the
> > two sheets:
>
> > Private Sub Workbook_Open()
> > Application.EnableEvents = True
>
> > With ThisWorkbook.Worksheets
> > * * .Add After:=Worksheets(Sheets.Count)
> > * * Worksheets(2).Range("E2:S2").Copy
> > Destination:=Worksheets(Sheets.Count).Cells(1, 1)
> > * * Worksheets(Sheets.Count).Visible = False
> > End With
> > Application.CutCopyMode = False
> > With ThisWorkbook.Worksheets
> > * * .Add After:=Worksheets(Sheets.Count)
> > * * Worksheets(Sheets.Count).Visible = False
> > End With
> > Worksheets(Sheets.Count).Name = "Summary"
> > End Sub- Hide quoted text -
>
> - Show quoted text -

Sorry, I was wrong. It's still a problem.

On Feb 3, 12:58*pm, John Smith <jamasm2...@gmail.com> wrote:
> On Feb 3, 11:49*am, Don Guillett <dguille...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > This code tested fine in xl2003 to add the two sheets on open and
> > delete on close. If you do not want the sheets created when you open
> > the assign to a button/shape. Or, send file to me at dguillett1
> > @gmail.com
>
> > Private Sub Workbook_Open()
> > ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> > Worksheets(2).Range("E2:S2").Copy Cells(1, 1)
> > ActiveSheet.Visible = False
>
> > ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> > ActiveSheet.Name = "Summary"
> > ActiveSheet.Visible = False
> > End Sub
>
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > With ActiveWorkbook
> > Application.DisplayAlerts = False
> > On Error Resume Next
> > * .Sheets("Summary").Delete
> > * .Worksheets(Sheets.Count).Delete
> > * .Save
> > End With
> > Application.Quit
> > End Sub
>
> > On Feb 3, 11:04*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > On Feb 3, 10:42*am, Don Guillett <dguille...@gmail.com> wrote:
>
> > > > Try this. No need to unhide or select or check or close if quitting
> > > > excel.
>
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > With ActiveWorkbook
> > > > Application.DisplayAlerts = False
> > > > On Error Resume Next
> > > > * .Sheets("Summary").Delete
> > > > * .Worksheets(Sheets.Count).Delete
> > > > * .Save
> > > > End With
> > > > Application.Quit
> > > > End Sub
>
> > > > On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > > > Hi,
> > > > > I'm trying to delete two sheets from a workbook that were added during
> > > > > a Workbook_Open event. In the close procedure I have the following
> > > > > code:
>
> > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > > Application.DisplayAlerts = False
> > > > > DoEvents
> > > > > On Error Resume Next
> > > > > If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> > > > > * * ActiveWorkbook.Worksheets("Summary").Visible = True
> > > > > * * ActiveWorkbook.Worksheets("Summary").Select
> > > > > * * ActiveWorkbook.Worksheets("Summary").Delete
> > > > > End If
>
> > > > > On Error GoTo 0
>
> > > > > Worksheets(Sheets.Count).Visible = True
> > > > > Worksheets(Sheets.Count).Select
> > > > > Worksheets(Sheets.Count).Delete
>
> > > > > Application.Caption = Empty
>
> > > > > ThisWorkbook.Save
> > > > > ThisWorkbook.Close
>
> > > > > Application.DisplayAlerts = True
> > > > > Application.Quit
> > > > > End Sub
>
> > > > > The problem is that when I open the workbook, I get a '1004' error
> > > > > because the summary sheet already exists. I put a counter in the
> > > > > Workbook_Open procedure to verify that it was only firing once and
> > > > > message box at the tail end of the Workbook_BeforeClose procedure
> > > > > asking for the last sheet name, which was correct. So, why can't I
> > > > > delete these two sheets?
> > > > > Thanks.
> > > > > James- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Thanks, Don, unfortunately that didn't work, so I assume that the
> > > problem really is in the Workbook_Open procedure, even though my
> > > counter only said it ran once. Here is the code that I use to add the
> > > two sheets:
>
> > > Private Sub Workbook_Open()
> > > Application.EnableEvents = True
>
> > > With ThisWorkbook.Worksheets
> > > * * .Add After:=Worksheets(Sheets.Count)
> > > * * Worksheets(2).Range("E2:S2").Copy
> > > Destination:=Worksheets(Sheets.Count).Cells(1, 1)
> > > * * Worksheets(Sheets.Count).Visible = False
> > > End With
> > > Application.CutCopyMode = False
> > > With ThisWorkbook.Worksheets
> > > * * .Add After:=Worksheets(Sheets.Count)
> > > * * Worksheets(Sheets.Count).Visible = False
> > > End With
> > > Worksheets(Sheets.Count).Name = "Summary"
> > > End Sub- Hide quoted text -
>
> > - Show quoted text -
>
> Don, I finally got this to work! I had to manually delete the sheets
> before the code would delete the sheets - even though the
> workbook_open procedure created them. Thank you for your help, because
> I really appreciate the time and effort that everyone gives me.
> James

I don't understand. Worked perfectly here. Sure, send the file
dguillett1 @gmail.com

On Feb 3, 5:28*pm, Don Guillett <dguille...@gmail.com> wrote:
> On Feb 3, 12:58*pm, John Smith <jamasm2...@gmail.com> wrote:
>
>
>
>
>
> > On Feb 3, 11:49*am, Don Guillett <dguille...@gmail.com> wrote:
>
> > > This code tested fine in xl2003 to add the two sheets on open and
> > > delete on close. If you do not want the sheets created when you open
> > > the assign to a button/shape. Or, send file to me at dguillett1
> > > @gmail.com
>
> > > Private Sub Workbook_Open()
> > > ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> > > Worksheets(2).Range("E2:S2").Copy Cells(1, 1)
> > > ActiveSheet.Visible = False
>
> > > ThisWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)
> > > ActiveSheet.Name = "Summary"
> > > ActiveSheet.Visible = False
> > > End Sub
>
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > With ActiveWorkbook
> > > Application.DisplayAlerts = False
> > > On Error Resume Next
> > > * .Sheets("Summary").Delete
> > > * .Worksheets(Sheets.Count).Delete
> > > * .Save
> > > End With
> > > Application.Quit
> > > End Sub
>
> > > On Feb 3, 11:04*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > > On Feb 3, 10:42*am, Don Guillett <dguille...@gmail.com> wrote:
>
> > > > > Try this. No need to unhide or select or check or close if quitting
> > > > > excel.
>
> > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > > With ActiveWorkbook
> > > > > Application.DisplayAlerts = False
> > > > > On Error Resume Next
> > > > > * .Sheets("Summary").Delete
> > > > > * .Worksheets(Sheets.Count).Delete
> > > > > * .Save
> > > > > End With
> > > > > Application.Quit
> > > > > End Sub
>
> > > > > On Feb 3, 10:01*am, John Smith <jamasm2...@gmail.com> wrote:
>
> > > > > > Hi,
> > > > > > I'm trying to delete two sheets from a workbook that were addedduring
> > > > > > a Workbook_Open event. In the close procedure I have the following
> > > > > > code:
>
> > > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > > > Application.DisplayAlerts = False
> > > > > > DoEvents
> > > > > > On Error Resume Next
> > > > > > If ActiveWorkbook.Worksheets(Sheets.Count).Name = "Summary" Then
> > > > > > * * ActiveWorkbook.Worksheets("Summary").Visible = True
> > > > > > * * ActiveWorkbook.Worksheets("Summary").Select
> > > > > > * * ActiveWorkbook.Worksheets("Summary").Delete
> > > > > > End If
>
> > > > > > On Error GoTo 0
>
> > > > > > Worksheets(Sheets.Count).Visible = True
> > > > > > Worksheets(Sheets.Count).Select
> > > > > > Worksheets(Sheets.Count).Delete
>
> > > > > > Application.Caption = Empty
>
> > > > > > ThisWorkbook.Save
> > > > > > ThisWorkbook.Close
>
> > > > > > Application.DisplayAlerts = True
> > > > > > Application.Quit
> > > > > > End Sub
>
> > > > > > The problem is that when I open the workbook, I get a '1004' error
> > > > > > because the summary sheet already exists. I put a counter in the
> > > > > > Workbook_Open procedure to verify that it was only firing once and
> > > > > > message box at the tail end of the Workbook_BeforeClose procedure
> > > > > > asking for the last sheet name, which was correct. So, why can't I
> > > > > > delete these two sheets?
> > > > > > Thanks.
> > > > > > James- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Thanks, Don, unfortunately that didn't work, so I assume that the
> > > > problem really is in the Workbook_Open procedure, even though my
> > > > counter only said it ran once. Here is the code that I use to add the
> > > > two sheets:
>
> > > > Private Sub Workbook_Open()
> > > > Application.EnableEvents = True
>
> > > > With ThisWorkbook.Worksheets
> > > > * * .Add After:=Worksheets(Sheets.Count)
> > > > * * Worksheets(2).Range("E2:S2").Copy
> > > > Destination:=Worksheets(Sheets.Count).Cells(1, 1)
> > > > * * Worksheets(Sheets.Count).Visible = False
> > > > End With
> > > > Application.CutCopyMode = False
> > > > With ThisWorkbook.Worksheets
> > > > * * .Add After:=Worksheets(Sheets.Count)
> > > > * * Worksheets(Sheets.Count).Visible = False
> > > > End With
> > > > Worksheets(Sheets.Count).Name = "Summary"
> > > > End Sub- Hide quoted text -
>
> > > - Show quoted text -
>
> > Don, I finally got this to work! I had to manually delete the sheets
> > before the code would delete the sheets - even though the
> > workbook_open procedure created them. Thank you for your help, because
> > I really appreciate the time and effort that everyone gives me.
> > James
>
> I don't understand. Worked perfectly here. Sure, send the file
> dguillett1 @gmail.com- Hide quoted text -
>
> - Show quoted text -

Sorry, Don, it worked perfectly for me, too, after I decided to stop
being stupid.