How to remove everything but one sheet?

[chamster]

I run the following code

For Each ws In Worksheets
If ws.Name <> "Settings" Then ws.Delete
Next ws

and to my surprise, there are still some sheets left after the loop. It's a number of charts (located on separate sheets). Those are not removed. Why? How do i get to them?

First i thought i should loop through all the sheets, not worksheets but as far i can see, there's no datatype Sheet, only Worksheet.

[mdmackillop]

Dim ws
For Each ws In Sheets
If ws.Name <> "Settings" Then ws.Delete
Next ws

[johnske]

You mean chartsheets? Sheets is the general form,e,g, Dim Sheet As Object

For Each Sheet In Sheets
If Not Sheet.Name = "Settings" Then Sheet.Delete
Next
{N.B. Sheets is a reserved word, Sheet is not and should be declared as type object}

[chamster]

That easy? Thanks.

Also, did you mean
"Sheet is not and should not be declared as type object", by any chance?

[rory]

"Sheet is not [a reserved word,] and [it] should be declared as type object [rather than type Sheet or anything else]"

[johnske]

no, i meant "Sheets" (plural) is a reserved word, "Sheet" (singular) is not a reserved word and can be declared as a variable of type object

[Bob Phillips]

But a wise man wouldn't do that!

[chamster]

"Sheet is not [a reserved word,] and [it] should be declared as type object [rather than type Sheet or anything else]"

It took me a while but then it went through. Thanks.

[lior03]

hello
how can i delete all sheets in a workbook but the activeshet i am in?
thanks

[Norie]

Why not just copy the active sheet to a new workbook?

ActiveSheet.Copy

[Bob Phillips]

Application.DisplayAlerts = False
For Each sh In Activeworkbook.Worksheets
If sh.Name <> Activesheet.Name Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

[TonyJollans]

Sheets is not a reserved word.

There are some reserved words but they are all VBA keywords; Sheets is part of the Excel object model, not VBA.

Both the following routines are valid, if unusual and unwise, code:

Sub Sheets()
Dim Sheets As Sheets
Set Sheets = ActiveWorkbook.Sheets
For Each Sheet In Sheets
MsgBox Sheet.Name
Next
End Sub

Sub Sheets2()
Dim Sheets As Workbook
Set Sheets = ActiveWorkbook
For Each Sheet In Sheets.Sheets
MsgBox Sheet.Name
Next
End Sub

[johnske]

pardon my loose terminology chamster, but by 'reserved' i really meant that Sheets is a 'word' that can be found in a library in the object browser and is thus part of the object model. As it IS part of the object model; to avoid confusing other coders, its use should be considered to be reserved and 'off limits' for use as a variable name (although Visual Basic itself is not at all confused by such usage).

i had just hoped to avoid a lot of typing - seems not...

[malik641]

Sheets is not a reserved word.

There are some reserved words but they are all VBA keywords; Sheets is part of the Excel object model, not VBA.

Both the following routines are valid, if unusual and unwise, code:

Sub Sheets()
Dim Sheets As Sheets
Set Sheets = ActiveWorkbook.Sheets
For Each Sheet In Sheets
MsgBox Sheet.Name
Next
End Sub

Sub Sheets2()
Dim Sheets As Workbook
Set Sheets = ActiveWorkbook
For Each Sheet In Sheets.Sheets
MsgBox Sheet.Name
Next
End Sub
Very Interesting, Tony. Thanks for the info. I never thought if it like that :)

[TonyJollans]

I agree completely that one should not use names from the object model, but I do think it best if people understand the difference - I'm not really picky, honestly I'm not :)

I spend most of my time in Word - I find it far more interesting than Excel - and Word does not have a Sheets collection or any other Sheets object or property or method. In Word I could (and might) use Sheets without causing any confusion.

It simply is not possible for VBA itself to consider keywords in object models off-limits. Think what would happen if you added a Reference to a Project and suddenly found lots of compile errors thrown up. Or what if Microsoft added a new object to the OM?