PDA

View Full Version : Delete visible sheets except sheet1



farrukh
12-12-2011, 04:13 AM
Hi All,

I am trying to delete the visble sheets except sheet 1 i manage to get delete all workbook except sheet1 any one can pleae modify . i need to delete visble sheets except sheet 1 all the hidden sheet will remain in the workbook.

Sub DeleteSheets()

Dim wksCurr As Worksheet

Application.DisplayAlerts = False
For Each wksCurr In ThisWorkbook.Worksheets
If wksCurr.Index <> 1 Then
wksCurr.Delete
End If
Next wksCurr
Application.DisplayAlerts = True

End Sub

Thanks
farrukh

Aflatoon
12-12-2011, 04:31 AM
Something akin to this:
Sub DeleteSheets()

Dim wksCurr As Worksheet

Application.DisplayAlerts = False
For Each wksCurr In ThisWorkbook.Worksheets
If wksCurr.Index <> 1 And wksCurr.Visible = xlSheetVisible Then
wksCurr.Delete
End If
Next wksCurr
Application.DisplayAlerts = True

End Sub

Bob Phillips
12-12-2011, 07:07 AM
Sub DeleteSheets()
Dim wksCurr As Worksheet

Application.DisplayAlerts = False

With ThisWorkbook

For Each wksCurr In .Worksheets

If wksCurr.Index = 1 Then

wksCurr.Visible = xlSheetVisible 'just in case
ElseIf wksCurr.Visible = xlSheetVisible Then

wksCurr.Delete
End If
Next wksCurr
End With

Application.DisplayAlerts = True
End Sub