Delete Multiple Worksheets

[B.M.Spell]

Good Morning
I have a workbook which uses a loop funtion in a macro to
collect data for a monthly history report. Sheets are created for every day
of the month, then a final summary sheet with the totals for all days. How
do I create a macro to delete all the sheets which were created for every
day of the month, but keep the summary sheet. Thanks

 

[Bob Phillips]

Application.DisplayAlerts = False
For Each sh In ActiveWorkbok.Worksheets
If sh.Name <> "Summary" Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

Try this one

Sub test()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
If LCase(Trim(sh.Name)) <> "summary" Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True
End Sub

 

[Patrick Molloy]

DIM WS as Worksheet
DIM bKeep As Boolean
Application.DisplayAlerts = False
For Each WS on Worksheets
''''test '''
If not bKeep then
WS.delete
End If
Next
Application.DisplayAlerts = True


now the test depends on you - how did you mark the
summary worksheet?
The test might be the sheet name...
bKeep = ws.Name="summary"

or a cell in th esheet with the word Summary in it
bKeep = ws.Range("A1").Value ="summary"

as its bolean, bKeep will be false by defualt unless the
test sets it to true. if true, the the ws is not deleted

atrick Molloy
Microsoft Excel MVP