Delete Worksheet

March 5, 2009 by vba excel
Filed under: Excel Vba 
Sponsor

Excel Vba Question:

I have a excel vba macro that sometimes create a separate sheet entitled “MC TABLE” depending on the data, will . I also have a second excel vba Macro within the same program that will delete and clear the needed sections of the different worksheets so that it can be ready for the next batch of data on the next day. The problem is that the “MC TABLE” sheet needs to be deleted entirely but on the occasions that it does not exist, I always get an error message. How can I write the excel vba macro code so that it tests to see if that sheet exists before attempting to select or delete it??

Excel Vba Answer:

Try this excel vba to delete worksheet, you don’t need to check, simply do this:

Sub marine3()
Application.DisplayAlerts = False
On Error Resume Next
Sheets(”MC TABLE”).Delete
Application.DisplayAlerts = True
On Error GoTo 0
End Sub

But if you want to:

Sub marine()
Dim WS As Worksheet
On Error Resume Next
Set WS = Sheets(”MC TABLE”)
On Error GoTo 0
If Not WS Is Nothing Then
MsgBox “I exist”
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
Else
MsgBox “I don’t exist”
End If
End Sub

  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • E-mail this story to a friend!
  • Live
  • MisterWong
  • Propeller
  • StumbleUpon
  • Technorati
  • TwitThis
  • YahooMyWeb
  • Print this article!

Rate This Tips:

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading ... Loading ...


Incoming excel search terms

vba delete worksheet,excel vba delete worksheet,delete worksheet vba,excel vba delete sheet,vba delete sheet,mss,delete worksheets vba,vba excel delete worksheet,delete a worksheet vba,excel vba worksheet delete,delete sheet in excel vba,delete worksheet excel vba,vba delete worksheet if it exists,vba excel delete sheet,excel vba delete a worksheet,excel vba delete worksheets,vba delete sheet with name,vba delete worksheets,delete sheet excel vba,vba delete a worksheet,ws excel visual basic select,excel delete worksheet vba,delete a worksheet in vba,delete excel sheet vba,delete sheet vba excel,delete worksheet,delete worksheet vba excel,delete worksheets with vba,excel delete sheet vba,excel delete worksheet,excel macro delete worksheet,excel vb delete sheet,excel worksheet delete vba,macro to check worksheet name before deleting it,vba clear excel deleted objects,vba delete sheet if exists,vba excel worksheet delete,vba worksheet delete,,ca,create worksheet if does not exist excel vba,delete a sheet in excel vba,delete sheet in vba,delete sheet vba,delete worksheet in vba,delete worksheets in excel vba,delete worksheets with name beginning in vba,deleting a sheet in vba,delte worksheet vba,excel before creating new sheet check if sheet exists



Related Excel Tips

Tags: , , , , , , , , , , , , , ,

Comments

One Comment on Delete Worksheet

  1. Phil_P on Fri, 17th Jul 2009 2:42 pm

    2. I have a function that reads the names of worksheets in a different workbook and should delete the worksheets based on the worksheet name. The code below runs through the expected lines to completion, but when I open the other workbook, all the ‘deleted’ worksheets are still there. What am I missing? Thanks.

    Function Sheet_Cleanup(oWorkBook As Object) As Integer

    Dim oSheetName As Object
    Dim sSheetID As String
    Dim oWorksheetID As Object

    ‘turn alerts off
    Application.DisplayAlerts = False

    ‘get the count of all the worksheets in the excel file
    iNewSheetCount = oWorkBook.Sheets.Count

    ’step through each worksheet from the end
    For iSheetCounter = iNewSheetCount To 1 Step -1

    ‘get sheet name
    sSheetID = oWorkBook.Sheets(iSheetCounter).Name

    ‘if the current sheet is a ‘raw’ sheet then…
    If InStr(sSheetID, “Raw”) > 0 Then

    ’set focus to the current sheet
    Set oWorksheetID = oWorkBook.Sheets(sSheetID)

    ‘delete the current sheet
    oWorksheetID.Delete

    End If

    ‘if the current sheet is a ‘data’ sheet then…
    If InStr(sSheetID, “Data”) > 0 Then

    ’set focus to the current sheet
    Set oWorksheetID = oWorkBook.Sheets(sSheetID)

    ‘if there is no data written to the worksheet then…
    If oWorksheetID.Cells(3, 2).Value = “” Then

    ‘delete the current sheet
    oWorksheetID.Delete

    End If

    End If

    Next iSheetCounter

    ’save changes
    oWorkBook.Save

    ‘turn alerts back on
    Application.DisplayAlerts = True

    End Function

Have another excel answer or questions for this problem ?
Feel free to post it here..