Can't delete sheets

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

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








- Show quoted text -
Click to expand...

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






- Show quoted text -
Click to expand...

I can send you the file, but it's over 5.5MB. Is that okay?
 
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






- Show quoted text -
Click to expand...

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






- Show quoted text -
Click to expand...

Sorry, I was wrong. It's still a problem.
 
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
Click to expand...

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

- Show quoted text -
Click to expand...

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