Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
code to delete sheets not in use based on a cell's drop down list
Hi
I have a cell with a drop down box containing the list of all of the worksheets in my workbook. When a worksheet name from this list is selected, I would like to write some code that will delete all of the other worksheets on closing. I have no idea where to start, and am desperate for some help. Please, please help. Nat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
code to delete sheets not in use based on a cell's drop down list
On Sep 19, 7:14 pm, kangasnat
wrote: Hi I have a cell with a drop down box containing the list of all of the worksheets in my workbook. When a worksheet name from this list is selected, I would like to write some code that will delete all of the other worksheets on closing. I have no idea where to start, and am desperate for some help. Please, please help. Nat So, you don't want the sheet deletion process to happen until the time of closing, correct? If so, use the Workbook_BeforeClose event of the ThisWorkbook module. Change sheet names and cells references accordingly. Also, keep in mind that there is no error handling in this, so you would need to add that. HTH Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wsDropDown As Worksheet, wsKeepSheet As Worksheet Dim wks As Worksheet 'sheet containing the dropdown Set wsDropDown = Sheets("DropDown") 'sheet selected from dropdown (change to cell containing dropdown) Set wsKeepSheet = Sheets(wsDropDown.Range("A1").Text) For Each wks In ActiveWorkbook.Worksheets If wks.Name < wsDropDown.Name And _ wks.Name < wsKeepSheet.Name Then Application.DisplayAlerts = False wks.Delete Application.DisplayAlerts = True End If Next wks Set wsDropDown = Nothing Set wsKeepSheet = Nothing End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
code to delete sheets not in use based on a cell's drop down l
Thank you for your very prompt response. I understand now!
What a legend! Nat "JW" wrote: On Sep 19, 7:14 pm, kangasnat wrote: Hi I have a cell with a drop down box containing the list of all of the worksheets in my workbook. When a worksheet name from this list is selected, I would like to write some code that will delete all of the other worksheets on closing. I have no idea where to start, and am desperate for some help. Please, please help. Nat So, you don't want the sheet deletion process to happen until the time of closing, correct? If so, use the Workbook_BeforeClose event of the ThisWorkbook module. Change sheet names and cells references accordingly. Also, keep in mind that there is no error handling in this, so you would need to add that. HTH Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wsDropDown As Worksheet, wsKeepSheet As Worksheet Dim wks As Worksheet 'sheet containing the dropdown Set wsDropDown = Sheets("DropDown") 'sheet selected from dropdown (change to cell containing dropdown) Set wsKeepSheet = Sheets(wsDropDown.Range("A1").Text) For Each wks In ActiveWorkbook.Worksheets If wks.Name < wsDropDown.Name And _ wks.Name < wsKeepSheet.Name Then Application.DisplayAlerts = False wks.Delete Application.DisplayAlerts = True End If Next wks Set wsDropDown = Nothing Set wsKeepSheet = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code help, delete rows based on column criteria | Excel Discussion (Misc queries) | |||
delete used records from drop down list? | Excel Worksheet Functions | |||
Code to an in cell drop down list | Excel Discussion (Misc queries) | |||
How to add and delete items from a list box or drop down box? | Excel Discussion (Misc queries) | |||
How do I color code items in a drop down list? | Excel Discussion (Misc queries) |