Delete sheets code error requires object

Giganews Newsgroups
Subject: Delete sheets code error requires object
Posted by:  Howard (lhkitt…@comcast.net)
Date: Mon, 2 Dec 2013

I found this code that makes a new workbook with one sheet, modified it and deleted some unwanted stuff and added code to do two sheets.  Changed the
'ws name' lines to include a date and the format.

The code near the bottom to get rid of any sheet not "named as" was originally for just one new sheet.  If s.Name <> ws1.Name Then s.Delete

I added for the second sheet but now those lines error out with Object Required.

The Application.DisplayAlerts = False both before AND afterwards is as the code was displayed.  This is puzzling to me.  Don't know if it is a typo or for real.

When I noticed it I changed the last one to True, but still get the error.
I want to get rid of the default sheet1 to sheet3.

I'm sure there are some Dim statement I don't need, but was going to wait until code worked as I wanted, then dismiss the un-needed ones.

Thanks,
Howard

Option Explicit

Sub aBook()

Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim s As Variant
Dim btn As OLEObject
Dim lLines As Long

Application.ScreenUpdating = False
    Set wb = Workbooks.Add

    Set ws1 = Worksheets.Add
    ws1.Name = Format(Date, "[$-409]d-mmm-yy;@") & " First Choice"

    Set ws2 = Worksheets.Add
    ws2.Name = Format(Date, "[$-409]d-mmm-yy;@") & " Second Choice"

  '  Application.DisplayAlerts = False
  '  For Each s In wb.Worksheets
  '    If s.Name <> ws1.Name Then s.Delete
  '    If s.Name <> ws2.Name Then s.Delete
  ' Next s
  ' Application.DisplayAlerts = False

Application.ScreenUpdating = True
End Sub

Replies