ExcelBanter - Deleting a sheet if it exists?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Deleting a sheet if it exists? (https://www.excelbanter.com/excel-programming/356305-deleting-sheet-if-exists.html)

Deleting a sheet if it exists?

Me again!

Flying along nicely, apart from...

In a macro i have:

Sheets("Completed Orders").Delete
Sheets("Partially Arrived Orders").Delete
Sheets("Draft Orders").Delete
Sheets("Placed Orders").Delete

But coming from another macro that's already deleted them (in som
cases only)..

How would i say

IF sheet "completed orders" exists then delete else not to worry

Thanks al

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=52354

Deleting a sheet if it exists?

drucey wrote:
Me again!

Flying along nicely, apart from...

In a macro i have:

Sheets("Completed Orders").Delete
Sheets("Partially Arrived Orders").Delete
Sheets("Draft Orders").Delete
Sheets("Placed Orders").Delete

But coming from another macro that's already deleted them (in some
cases only)..

How would i say

IF sheet "completed orders" exists then delete else not to worry

Thanks all

By wrapping the code in an error trapping block, for instance

Sub DeleteIfExists(ByVal SheetName as String)
On Error Resume Next
Sheets(SheetName).Delete
Err.Clear
End Sub

Deleting a sheet if it exists?

my god! i love this place!

thank you so much :

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=52354

Deleting a sheet if it exists?

Just a word of warning. If sheet4 doesn't exist, then the .delete will fail and
none of those worksheets will be deleted.

I think I'd just use:

Sub deletesheets()
Application.DisplayAlerts = False
On Error Resume Next
sheets("sheet1").delete
sheets("sheet2").delete
sheets("sheetetc").delete
on error goto 0
application.displayalerts = true
End Sub

ps. to the OP: remember that there has to be at least one visible sheet when
you're done.

Don Guillett wrote:

try

Sub deletesheets()
Application.DisplayAlerts = False
On Error Resume Next
myarray = Array("sheet1", "sheet4", "sheetetc")
Sheets(myarray).Delete
End Sub

--
Don Guillett
SalesAid Software

"drucey" wrote in
message ...

Me again!

Flying along nicely, apart from...

In a macro i have:

Sheets("Completed Orders").Delete
Sheets("Partially Arrived Orders").Delete
Sheets("Draft Orders").Delete
Sheets("Placed Orders").Delete

But coming from another macro that's already deleted them (in some
cases only)..

How would i say

IF sheet "completed orders" exists then delete else not to worry

Thanks all

--
drucey
------------------------------------------------------------------------
drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=523544

--

Dave Peterson