Hi Jim,
I looked at it for some time trying to figure out why it's not working.
Aparently, there's a non-existant worksheet called Bill001 on the VBA Project
Explorer. Since it does not exist in excel, it kicks out of the loop when the
code tries to delete the nonexistant worksheet. I slapped on an if statement
to delete all worksheets except for that one, and it finishes the loop with
the correct actions of deleting the page. Any idea how to get rid of the
nonexistant worksheet "Bill001" on the VBA Project Explorer.
Thanks for your help,
thomas

"Jim Thomlinson" wrote:

Is your workbook protected. If the book is protected (not the sheet but the
book) then you can not delete any sheets...
Add this code...

Sub DeleteSheets()
Dim wks As Worksheet
On Error GoTo ErrorHandler

Thisworkbook.unprotect 'Add a password if necessary
Application.DisplayAlerts = False
For Each wks In Worksheets
If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete
Next wks
Application.DisplayAlerts = True

exit sub
ErrorHandler:
Application.DisplayAlerts = True
msgbox "Abnormal termination..."
End Sub
--
HTH...

Jim Thomlinson


"thomas" wrote:

No just one msgbox. I get only two. "let the deleting begin" and then that
one. I think I figured it out though. Just don't know how to solve. I have a
loop to where it makes copies of the bill sheet which I posted just a second
ago. Please read that post and tell me what you think.
Thanks,
Thomas

"Jim Thomlinson" wrote:

Ok you have lost me. You can only have one sheet named Bill001, but you are
saying that you get two message boxes saying "Bill001 Delete"?
--
HTH...

Jim Thomlinson


"thomas" wrote:

I got to remember to use msgbox to help solve problems. It's not going
through the code because there is no worksheet of "Bill001" Which is the
second (also is the last) msgbox which says "Bill001 Delete". Can't delete
because it's not there so it breaks out of the code, I guess. Any ideas on
how to fix.
Thank you,
Thomas

"Jim Thomlinson" wrote:

Here is the code with a couple of message boxes. Let me know if when the code
is run if any message boxes pop up or not...

Sub DeleteSheets()
Dim wks As Worksheet
On Error GoTo ErrorHandler

msgbox "Let the deleteing begin..."
Application.DisplayAlerts = False
For Each wks In Worksheets
If InStr(UCase(wks.Name), "BILL") 0 Then msgbox wks.name & _
" Delete"
If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete
Next wks
msgbox "All Done..."
ErrorHandler:
Application.DisplayAlerts = True

End Sub

--
HTH...

Jim Thomlinson


"thomas" wrote:

I must be doing something wrong because the code has no response. Perhaps
it's how I call the DeleteSheets(). Can you give me an example of how to call
it.
Thank you,
Thomas

"Jim Thomlinson" wrote:

My fault on your last thread... I just posted the response...

Sub DeleteSheets()
Dim wks As Worksheet
On Error GoTo ErrorHandler

Application.DisplayAlerts = False
For Each wks In Worksheets
If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = True

End Sub

--
HTH...

Jim Thomlinson


"thomas" wrote:

I get an error Visual Basic "400" message when this code is ran:

Sub DeleteSheets()
Dim wks As Worksheet

Application.DisplayAlerts = False
For Each wks In Worksheets
If InStr("Bill (", wks.Name) 0 Then wks.Delete
Next wks

Application.DisplayAlerts = True

End Sub


How can I fix this?

Thank you,
Thomas