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 (1 votes, average: 5.00 out of 5)
Loading ... Loading ...


Incoming excel search terms

excel vba delete worksheet,excel vba delete sheet,delete worksheet vba,vba delete worksheet,delete sheet in excel vba,vba excel delete sheet,excel vba worksheet delete,ws excel visual basic select,delete sheet excel vba,delete sheet vba excel,delete worksheet excel vba,delete worksheet vba excel,delete worksheets with vba,excel worksheet delete vba,macro to check worksheet name before deleting it,vba delete sheet,vba excel delete worksheet,vba excel worksheet delete,vba worksheet delete,create worksheet if does not exist excel vba,delete a sheet in excel vba,delete excel sheet vba,delete worksheets with name beginning in vba,excel before creating new sheet check if sheet exists,excel delete sheet vba,excel delete worksheet vba,excel macro check if sheet exists,excel macro delete sheets,excel vba delete worksheets,excel vba dont delete worksheet if value in name,excel vba error worksheet does not exist,excel vba ws delete,find out if worksheet exists and if it does rename and delete,microsoft visual basic,mss,vb get sheet name,vba delete worksheet if it exists,vba excel delete error worksheets,vba macro set focus to sheet,vba name sheet delete,visual basic vba code how to delete the worksheet or tab in excel worksheet(2) to (5) range of sheet,,0h,1,2,access delete worksheet,access vb delete worksheets,access vba check if excel work sheet exists,access vba delete current excel sheet,access vba excel delete worksheet



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..