Delete if there is a tab

[Djblois]

Here is my code:

[vba] On Error GoTo No_Cust
cust.delete
Set cust = Nothing
No_Cust:
On Error GoTo No_Prod
prod.delete
Set prod = Nothing
No_Prod:
On Error GoTo No_Ship
ship.Delete
Set ship = Nothing
No_Ship:[/vba]

I want it to delete if there is a TAb with those names and if not go to the next one. However I keep getting an error on the ship.Delete
Please explain what is wrong if it is complicated to figure out.

Daniel

[Norie]

Daniel

What is ship?

I don't see it defined anywhere in the code, is it a sheet's codename?

[Djblois]

It is a sheet name. Same as Prod and Cust. I want it to delete Cust if it exists and if not go to Prod. Then Delete Prod if it exists and if not go to Ship. Finally, delete Ship if it exists if not go to my next code.

Daniel

[jungix]

Are they the only 3 worksheets you have? Because you have to keep at least one, you can't delete all worksheets from a workbook.

[Norie]

Daniel

So is it a sheet codename?

What error are you getting?

[asingh]

You could use something like this...it will loop through all your tabs search for the sheet specified...and delete it...

[VBA]Sub Del_Sheets()

Dim i As Integer
Dim Sheet_Flag As Boolean

Sheet_Flag = False

For i = 1 To Sheets.Count
If Sheets(i).Name = "SHEET_NAME_SPECIFIED" Then
Sheet_Flag = True
End If
Next

If Sheet_Flag = True Then
Application.DisplayAlerts = False
Sheets("SHEET_NAME_SPECIFIED").Delete
Application.DisplayAlerts = True
End If


End sub[/VBA]

[Zack Barresse]

If that is all you are doing, you can just ignore the error, as it won't delete all worksheets from the workbook (as said, you need at least 1) ...

[vba]Dim ws as worksheet, sTmp as string
application.displayalerts = false
on error resume next
For each ws in Thisworkbook.worksheets
select case ws.codename
case "cust", "prod", "ship"
ws.delete
end select
if err <> 0 then
sTmp = ws.name
err.clear
end if
next ws
if stmp <> vbnullstring then
thisworkbook.sheets.add
thisworkbook.sheets(stmp).delete
application.displayalerts = true
end if[/vba]