PDA

View Full Version : Delete Sheets



ssinghal
07-10-2008, 05:28 AM
I want to delete the following sheets with a macro, but all do not always exist. I need to delete if it is there:

Sheet names:

Stratford
Castleberry
Spartanburg
Greenville
Durham
Tulsa
Lakeland


Thanks!

Oorang
07-10-2008, 05:52 AM
Option Explicit

Public Sub Example()
DeleteWorksheets ActiveWorkbook, "Stratford", "Castleberry", _
"Spartanburg", "Greenville", "Durham", "Tulsa", "Lakeland"
End Sub

Public Sub DeleteWorksheets(ByRef parentWorkbook As Excel.Workbook, _
ParamArray worksheetNames() As Variant)
Const lngLwrBnd_c As Long = 0
Dim lngCrntWrksht As Long
For lngCrntWrksht = lngLwrBnd_c To UBound(worksheetNames)
If WorksheetExists(parentWorkbook, worksheetNames(lngCrntWrksht)) Then
parentWorkbook.Worksheets(worksheetNames(lngCrntWrksht)).Delete
End If
Next
End Sub

Private Function WorksheetExists(ByRef parentWorkbook As Excel.Workbook, _
ByVal worksheetName As String _
) As Boolean
On Error Resume Next
WorksheetExists = Not parentWorkbook.Worksheets(worksheetName) Is Nothing
End Function