On Feb 3, 11:49*am, Don Guillett 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 wrote:



On Feb 3, 10:42*am, Don Guillett 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 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.