Deleting Sheets Based on Name

Ive got a macro which makes a large number of sheets with information in
them....

Ive then got another macro which gets all the information from those
sheets....

the problem is... im then left with all the sheets.. and I really want to
delete them.....

Is there anyway to make a macro which would let me delete sheets.. based on
their name.....

all the ones i want to delete are called "cats (104)" or "cats
(105)" etc etc..... there is one called "cats" which i want to keep.... so
basically If it could someonie delete based on the term "cats ("

any ideas?

The important thing to know is to itarate from top to bottom to avoid problems due to the re-indexing of the worksheets by a worksheet delete

in other words if you delete worksheets(3)
worksheets(4) becomes worksheets(3)

Please Login or Register  to view this content.

I disn't test it but it should work

One way:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim ErrCounter As Long

ErrCounter = 0

On Error Resume Next
Application.DisplayAlerts = False
For Each wks In ActiveWorkbook.Worksheets
If LCase(wks.Name) Like "cats (*" Then
wks.Delete
If Err.Number <> 0 Then
ErrCounter = ErrCounter + 1
Err.Clear
End If
End If
Next wks
Application.DisplayAlerts = True
On Error GoTo 0

If ErrCounter > 0 Then
MsgBox ErrCounter & " sheets were not deleted"
End If

End Sub



The Boondock Saint wrote:
>
> Ive got a macro which makes a large number of sheets with information in
> them....
>
> Ive then got another macro which gets all the information from those
> sheets....
>
> the problem is... im then left with all the sheets.. and I really want to
> delete them.....
>
> Is there anyway to make a macro which would let me delete sheets.. based on
> their name.....
>
> all the ones i want to delete are called "cats (104)" or "cats
> (105)" etc etc..... there is one called "cats" which i want to keep.... so
> basically If it could someonie delete based on the term "cats ("
>
> any ideas?

--

Dave Peterson