error 400...

[thomas]

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

 

[Jim Thomlinson]

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

 

[thomas]

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]

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

 

[thomas]

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

 

[thomas]

just a thought...I guess there might be a "Bill001" because of the code I
used to make copies of a Worksheet:

Option Explicit

Function AddSheets()
Dim ws As Worksheet
Set ws = Worksheets("Bill")
ws.Copy Worksheets(1)
SetSheetName Worksheets(1)
End Function
Private Sub SetSheetName(ws As Worksheet)
On Error Resume Next
Dim sname As String
Dim index As Long
index = 0
Do
Err.Clear
index = index + 1
sname = "Bill" & Format$(index, "000")
ws.Name = sname
Loop While Err.Number <> 0
End Sub


But the sheet's name in Excel reads "Bill (2)". Is there a way to work
around this?

 

[Jim Thomlinson]

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"?

 

[thomas]

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]

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

 

[thomas]

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]

I assume that that sheet is set to xlVeryHidden. This can be seen in the
Properties window when that sheet is selected.

sub ShowSheet
sheets("Bill001").visible = true

end sub

 

[thomas]

There was something wrong with my copy command. It was changing the name of
my xlVeryHidden sheets. I'm not sure why. I guess lack of understanding of
code. So I modified it to where it made since to me, and it works. I didnt'
think you could ever modify xlVeryHidden Sheets. I guess you can in this
case. Weird. Thanks for all of your help.
Thomas