deleting sheets
 

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

deleting sheets
 

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

Jim Thomlinson


"greg" wrote:

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

deleting sheets
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"greg" wrote in message ...
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

deleting sheets
 

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" wrote:

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

deleting sheets
 

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



"greg" wrote in message
...
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

deleting sheets
 

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

Jim Thomlinson


"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"greg" wrote in message ...
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

deleting sheets
 

LOL

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jim Thomlinson" wrote in message
...
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...
--
HTH...

Jim Thomlinson


"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"greg" wrote in message ...
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