Macro for Deleting Worksheets

[andiam24]

Hi,

I am somewhat new to VBA so the simpler the better. I have a workbook with a
summary page and corresponding tabs for data entry. On the summary page the
user selects which columns to keep for data display. The columns retained
should correspond with the tabs as this is where the data from the tabs will
be displayed. Is it possible to use a macro to delete all tabs that do not
correspond to a column the user has selected? Any help would be great.

 

[Gary''s Student]

Say we are on the summary tab. Say each column is associated with a data
sheet tab. Say that row 1 contains the tabname:

A1 contains Sheet1
B1 contains Sheet2
C1 contains Sheet3
etc.

The user selects a column and runs:

Sub TabKiller()
s1 = ActiveSheet.Name
s2 = Selection(1).Value
For i = 1 To Columns.Count
v = Cells(1, i).Value
If v = "" Then Exit Sub
If v = s1 Or v = s2 Then
Else
Sheets(v).Delete
End If
Next
End Sub

 

[andiam24]

Hi Gary,

Thanks for the code but I couldn't figure if it assumed the tab name was the
same as the column name? And I tried it but got a subscript out of range
error which is the same error I got from the code I was trying before, which
follows:

The user selection mechanism is a checkbox located in row 6 (hence the
Value=FALSE) but I'm sure there's a better way.

Sub CommandButton1_Click()
Dim BTN As Button
Set BTN = ActiveSheet.Buttons(Application.Caller)
'deletes unused tabs and confirm delete dialog
Application.DisplayAlerts = False
If Workbooks("Sheet1").Range("d6").Value = False Then
Worksheets(Array("Sheet2", "Sheet10")).Delete
End If
If Workbooks("Sheet1").Range("e6").Value = False Then
Worksheets(Array("Sheet4", "Sheet12")).Delete
End If
If Workbooks("Sheet1").Range("f6").Value = False Then
Worksheets("Sheet6").Delete
End If
If Workbooks("Sheet1").Range("g6").Value = False Then
Worksheets(Array("Sheet3", "Sheet5", "sheet9")).Delete
End If
If Workbooks("Sheet1").Range("i6").Value = False Then
Worksheets("sheet21").Delete
End If
If Workbooks("Sheet1").Range("T6").Value = False Then
Worksheets(Array("Sheet7", "Sheet15", "sheet17", "sheet18")).Delete
End If
End Sub