Deleting Sheets

MrT82

Board Regular
Joined
Dec 12, 2005
Messages
84
Hi All,

Does anyone know of a macro that I can run that will delete all sheets in the workbook with the exception of a named sheet (that will always be constant)?

The problem I have that is I run a macro before this and therefore the number of sheets to delete always varies and when I recorded a macro to do this, it didn't like it!

Thanks,

Paul
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Something like this:

Code:
Sub Test()
    Const SheetToKeep As String = "Master"
    Application.DisplayAlerts = False
    Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Name <> SheetToKeep Then Sh.Delete
    Next Sh
    Application.DisplayAlerts = False
End Sub

Change the SheetToKeep constant to suit.
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
<hr>
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> KillSheets()
<SPAN style="color:#00007F">Dim</SPAN> S <SPAN style="color:#00007F">As</SPAN> Worksheet
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#007F00">'Uses sheet codenames in case a user renames the sheet.</SPAN>
<SPAN style="color:#007F00">'Sheet code name is the name in the VBE that is NOT in</SPAN>
<SPAN style="color:#007F00">'Parentheses.</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> S <SPAN style="color:#00007F">In</SPAN> Sheets
<SPAN style="color:#00007F">If</SPAN> S.CodeName <> "MySheet" <SPAN style="color:#00007F">Then</SPAN> S.Delete
<SPAN style="color:#00007F">Next</SPAN> S
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
<hr>
 

MrT82

Board Regular
Joined
Dec 12, 2005
Messages
84
I thought I could tweak the above and below in order that it would select all sheets with the exception of sheet 1 (which is constant) however it doesn't appear to work. Any ideas?

Sub SelectAll()
Dim S As Worksheet
Application.DisplayAlerts = False
For Each S In Sheets
If S.CodeName <> "Sheet1" Then S.Select
Next S
Application.DisplayAlerts = True
End Sub

Thanks,

Paul
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
That code is selecting each sheet. But it's selecting each sheet one at a time.
Try this
<hr>
Sub SelectAll()
Dim S As Worksheet
Application.DisplayAlerts = False
For Each S In Sheets
If S.CodeName <> "Sheet1" Then S.Select (False)
Next S
Application.DisplayAlerts = True
End Sub
<hr>
 

Watch MrExcel Video

Forum statistics

Threads
1,119,144
Messages
5,576,336
Members
412,718
Latest member
dragosm
Top