Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 sheets in workbook, but visible only one? | Excel Discussion (Misc queries) | |||
Delete all visible sheets in workbook... | Excel Programming | |||
How can I delete similar rows in excel workbook with many sheets? | Excel Worksheet Functions | |||
Need Help w/ Print Macro to Print All Visible Sheets (including Charts) in a Workbook | Excel Programming | |||
Code to make sheets in a workbook visible | Excel Programming |