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?

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

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

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


Code
-------------------
Dim Ws As Worksheet
Dim Count As Long, L As Long


Count = ActiveWorkbook.Worksheets.Count

For Count = ActiveWorkbook.Worksheets.Count To 1 Step -1
Set Ws = ActiveWorkbook.Worksheets(Count)
If StrComp(Left$((Ws.Name), 3), "Cat", vbTextCompare) Then
Ws.Delete
End If
Nex
-------------------


I disn't test it but it should wor

--
Dnere
-----------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...fo&userid=2618
View this thread: http://www.excelforum.com/showthread.php?threadid=39756