deleting sheets

[greg]

ok,
I am trying to delete all the sheets in a workbook.
I started with something like this:


Sub xxx()
Dim y As Excel.Worksheet
For x = 1 To Excel.ActiveWorkbook.Worksheets.Count
Set y = Excel.ActiveWorkbook.Worksheets(1)
y.Delete
Next x
End Sub


2 problems.
When you delete the sheet, you get a prompt. Is there a way to turn that
prompt off?

I guess I need at least 1 sheet. Is there a way to add a new sheet to the
end of the list of sheets. Then go
For x = 1 To Excel.ActiveWorkbook.Worksheets.Count -1

thanks on any help

 

[Guest]

Give this a whirl...

Sub xxx()
Dim wksNew As Worksheet
Dim wks As Worksheet
On Error GoTo ErrorHandler
Set wksNew = Worksheets.Add
Application.DisplayAlerts = False
For Each wks In ActiveWorkbook.Worksheets
If wks.Name <> wksNew.Name Then wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = True
End Sub

 

[Ron de Bruin]

Try this greg

Sub xxx()
Dim ws As Worksheet
Dim sh As Worksheet

Set ws = Worksheets.Add

Application.DisplayAlerts = False

For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> ws.Name Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True
End Sub

 

[Guest]

in responce to your 1st question

I think it is Application.DisplayAlerts = False
might well be wrong

would have to test teh rest
Might post back later if i get the chance

 

[greg]

THANKS EVERYONE!!!!!!!!!!!!

 

[Guest]

Geez Ron if you are going to steal my code you really need to give me credit...
vbg ;-)

It is almost spooky how similar our 2 solutions are. I must be reading too
many of your posts...

 

[Ron de Bruin]

LOL