Delete sheets code error requires object

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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
 
Hi Howard,

Am Mon, 2 Dec 2013 12:02:53 -0800 (PST) schrieb Howard:
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.
Click to expand...

the array s is not filled with the sheet names.
But it is easier to do it with For each wsh in ActiveWorkbook.Worksheets

Try:

Sub aBook()

Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wsh As Worksheet
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 wsh In ActiveWorkbook.Worksheets
If wsh.Name <> ws1.Name And wsh.Name <> ws2.Name Then wsh.Delete
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Regards
Claus B.
 
Hi Howard,



Am Mon, 2 Dec 2013 12:02:53 -0800 (PST) schrieb Howard:


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
Click to expand...
'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.
Click to expand...



the array s is not filled with the sheet names.

But it is easier to do it with For each wsh in ActiveWorkbook.Worksheets



Try:



Sub aBook()



Dim wb As Workbook

Dim ws1 As Worksheet, ws2 As Worksheet

Dim wsh As Worksheet

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 wsh In ActiveWorkbook.Worksheets

If wsh.Name <> ws1.Name And wsh.Name <> ws2.Name Then wsh.Delete

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub



Regards

Claus B.
Click to expand...


That clears that up nicely.

Thanks.
Howard
 
Back
Top