Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for Deleting Worksheets
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for Deleting Worksheets
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 -- Gary''s Student - gsnu200842 "andiam24" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for Deleting Worksheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting worksheets | Excel Discussion (Misc queries) | |||
Deleting pictures from multiple worksheets | Excel Discussion (Misc queries) | |||
Deleting blank worksheets | Excel Worksheet Functions | |||
Prompt before deleting worksheets? | Excel Discussion (Misc queries) | |||
Help deleting worksheets | Excel Discussion (Misc queries) |