Delete Multiple Worksheets
 

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

Delete Multiple Worksheets
 

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)

"B.M.Spell" wrote in message
...
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

Delete Multiple Worksheets
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"B.M.Spell" wrote in message ...
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

Delete Multiple Worksheets
 

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



-----Original Message-----
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


.