Searching and Deleting Workbook Tab

[SaintBobCat]

Hi there

Can someone help me with some vba code that opens a workbook, searches for a tab with a specific name and deletes the tab if it is there then closes the workbook.

I'm sure this would have been answered before and i have had a search of previous posts without luck.

Cheers!

 

[RichardMGreen]

This this (untested):-

Sub deleter()
    dim ws as worksheet
    for each ws in worksheets
        if ws.name="Insert Tab name here" then
            application.displayalerts=false
            ws.delete
            application.displayalerts=true
        endif
    next
end sub

This will search the open/active workbook for the specific name you enter.

 

[GTO]

Just before we start plinking code, is it always the same workbook (same fullname) or is the user to browse for it?

 

[VoG]

Try like this

Sub test()
Workbooks.Open Filename:="C:\test.xls)"
If WorksheetExists("Sheet1") Then
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
End If
ActiveWorkbook.Close savechanges:=True
End Sub



Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

 

[JackDanIce]

try, Something like:

Sub ImMissingMyBanana ()
Dim TabName as String
Dim i as Long
Dim SheetExists as Boolean
With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
End With
Workbooks.Open Filename:="path and name of your file"
On Error Goto NotFound
TabName = InputBox("Enter the name of the worksheet you want to delete: ")
For i = 1 to Worksheets.count
  If Sheets(i).Name = TabName Then
    If MsgBox("Please confirm you want to delete " & TabName) = vbYes Then
      Sheets(TabName).Delete
      SheetExists = True
      Exit For
    End If
  End If
Next i
If SheetExists Then
  MsgBox "Sheet: " & TabName" & " has been deleted"
Else
  MsgBox "Sheet: " & TabName" & " does not exist in this workbook"
End If
With Application
  .DisplayAlerts = True
  .ScreenUpdating = True
End With
End Sub