Deleting worksheets in a procedure

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I am trying to create a small procedure to delete
worksheets if they are present. What is the code to test
if said sheet exists, and then delete it if it exists?

Thx MUCH in advance,
Al
 
The following example deletes
sheets "Sheet5", "Sheet4", "Sheet1" if they exist. You can
easily modify to accomodate your sheet names and number.

Sub delete_sheets()
Dim sh(3) As String
sh(1) = "Sheet5"
sh(2) = "Sheet4"
sh(3) = "Sheet1"

On Error Resume Next
Application.DisplayAlerts = False

For i = 1 To 3
Sheets(sh(i)).Delete
Next

Application.DisplayAlerts = True
On Error GoTo 0
End Sub

Nikos Y. (nyannaco at in dot gr)
 
Al,

See below:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''
Public bSheetNameIsUnique, bSheetNameIsOK As Boolean

Sub DoSomething(ByVal SheetName As String)

If SheetIsUnique(NewSheetName) Then
GoTo DoAuto
Else
FileNameOnly = NewSheetName
GoTo DoManual
End If
End Sub
Function SheetIsUnique(ByVal SheetName As String)

On Error Resume Next
Dim ErrMsg As String
bSheetNameIsUnique = True
For iSheetCount = 1 To Sheets.Count
If LCase(Sheets(iSheetCount).Name) =
LCase(SheetName) Then
bSheetNameIsUnique = False
Exit For
End If
Next
SheetIsUnique = bSheetNameIsUnique

End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''
Hope that helps
Philippe
 
That was PERFECT!!!

Thx MUCH!!
-----Original Message-----
The following example deletes
sheets "Sheet5", "Sheet4", "Sheet1" if they exist. You can
easily modify to accomodate your sheet names and number.

Sub delete_sheets()
Dim sh(3) As String
sh(1) = "Sheet5"
sh(2) = "Sheet4"
sh(3) = "Sheet1"

On Error Resume Next
Application.DisplayAlerts = False

For i = 1 To 3
Sheets(sh(i)).Delete
Next

Application.DisplayAlerts = True
On Error GoTo 0
End Sub

Nikos Y. (nyannaco at in dot gr)
.
Click to expand...
 
Back
Top