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

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

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