Delete all visible sheets in workbook...
 

Hiding the sheets is an unnecesary step if all you wan to do is to delete the
sheets with the word Bill in the name. Try this code

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

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

Jim Thomlinson


"thomas" wrote:

I have the VBA hide all sheets that should not be deleted, and then have them
become visible after the deletion takes place. But if I always have to have a
sheet visible, is there a way to delete all of the ones that contain the word
"Bill" in it.
Thanks for you help Jim,
Thomas

"Jim Thomlinson" wrote:

At least one sheet must be visible at all times. Why do you want to get rid
of all visible sheets. It seems to me a spreadsheet without any visible
sheets is not much use. Did you want to have an intro sheet with no data on
it and delete all of the other visible sheets... That is fairly easy...
--
HTH...

Jim Thomlinson


"thomas" wrote:

COMMENTS
it's been about a month since I last posted, I think, so I think it's time
to question the pros again. I have a great database program made that prints
bills, keeps track of taxable income, and prorates people thanks to the help
from this Website. So A BIG THANK YOU to you all.

QUESTION
I just wanted to know if there is a way to delete all visible worksheets in
a workbook. I couldn't find any articles on this one, so i hope someone knows
the answer.

Thank you ahead of time,
Thomas

Delete all visible sheets in workbook...
 

My fault... I had the Instr arguments backwards... Sorry about that

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've read that solution in another post but thought it was bad because I
couldn't get it to work. I cut paste your code and assigned it to a button to
test it, but it doesn't delete any of the sheets with "Bill" in the name tag.
any ideas why. the sheets are named "Bill (2)", "Bill (3)" "Bill (4)", ect.
Thanks for you help,
Thomas

"Jim Thomlinson" wrote:

Hiding the sheets is an unnecesary step if all you wan to do is to delete the
sheets with the word Bill in the name. Try this code

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

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

Jim Thomlinson


"thomas" wrote:

I have the VBA hide all sheets that should not be deleted, and then have them
become visible after the deletion takes place. But if I always have to have a
sheet visible, is there a way to delete all of the ones that contain the word
"Bill" in it.
Thanks for you help Jim,
Thomas

"Jim Thomlinson" wrote:

At least one sheet must be visible at all times. Why do you want to get rid
of all visible sheets. It seems to me a spreadsheet without any visible
sheets is not much use. Did you want to have an intro sheet with no data on
it and delete all of the other visible sheets... That is fairly easy...
--
HTH...

Jim Thomlinson


"thomas" wrote:

COMMENTS
it's been about a month since I last posted, I think, so I think it's time
to question the pros again. I have a great database program made that prints
bills, keeps track of taxable income, and prorates people thanks to the help
from this Website. So A BIG THANK YOU to you all.

QUESTION
I just wanted to know if there is a way to delete all visible worksheets in
a workbook. I couldn't find any articles on this one, so i hope someone knows
the answer.

Thank you ahead of time,
Thomas

Delete all visible sheets in workbook...
 

1) Looks like Instr() has it's arguments reversed. We're searching WKS.NAME
for BILL, not vice versa. Should be:
If InStrUCase(wks.Name,"BILL") 0 Then wks.Delete

2) This could fail if you have Worksheet or Workbook protection in effect.
3) This will delete worksheets but not chart sheets, etc. (doesn't sound
like an issue, but....)
4) Make sure the button you've assigned the code to isn't on a sheet with
Bill (or BILL) in it's name or the code might stop prematurely :-).


HTH,
--
George Nicholson

Remove 'Junk' from return address.


"thomas" wrote in message
...
I've read that solution in another post but thought it was bad because I
couldn't get it to work. I cut paste your code and assigned it to a button
to
test it, but it doesn't delete any of the sheets with "Bill" in the name
tag.
any ideas why. the sheets are named "Bill (2)", "Bill (3)" "Bill (4)",
ect.
Thanks for you help,
Thomas

"Jim Thomlinson" wrote:

Hiding the sheets is an unnecesary step if all you wan to do is to delete
the
sheets with the word Bill in the name. Try this code

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

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

Jim Thomlinson


"thomas" wrote:

I have the VBA hide all sheets that should not be deleted, and then
have them
become visible after the deletion takes place. But if I always have to
have a
sheet visible, is there a way to delete all of the ones that contain
the word
"Bill" in it.
Thanks for you help Jim,
Thomas

"Jim Thomlinson" wrote:

At least one sheet must be visible at all times. Why do you want to
get rid
of all visible sheets. It seems to me a spreadsheet without any
visible
sheets is not much use. Did you want to have an intro sheet with no
data on
it and delete all of the other visible sheets... That is fairly
easy...
--
HTH...

Jim Thomlinson


"thomas" wrote:

COMMENTS
it's been about a month since I last posted, I think, so I think
it's time
to question the pros again. I have a great database program made
that prints
bills, keeps track of taxable income, and prorates people thanks to
the help
from this Website. So A BIG THANK YOU to you all.

QUESTION
I just wanted to know if there is a way to delete all visible
worksheets in
a workbook. I couldn't find any articles on this one, so i hope
someone knows
the answer.

Thank you ahead of time,
Thomas