VBA to delete a tab only if it exists

[gheyman]

How do I change this code so that if the workbook does not have a tab named: Material Ad Hoc that it skips the part where its copying and deleting?

Sub Macro3()
'
'********************************
'This is the section of the code that I want pass over if there isn't a tab named "Material Ad Hoc".  Then proceed and do the next section of the code.
'
    Sheets("Material Ad Hoc").Select
    Sheets("Material Ad Hoc").Copy After:=Sheets(9)
    Sheets("Material Ad Hoc").Select
    ActiveWindow.SelectedSheets.Delete


'*********************************

Dim ProposalReports As Workbook
    Dim CMCS As Variant


    Set ProposalReports = ThisWorkbook
    
    CMCS = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    
    If CMCS <> False Then
        Workbooks.Open (CMCS)
        Set CMCS = ActiveWorkbook
        
        
      Sheets("CMCS Bill of Materials").Select
            Sheets("Material Ad Hoc").Copy Before:=ThisWorkbook.Sheets(2)
    
    End If

End Sub

Thank you

 

[mumps]

Try:

Sub Macro3()
    If Evaluate("isref('" & "Material Ad Hoc" & "'!A1)") Then
        With Sheets("Material Ad Hoc")
            .Copy After:=Sheets(9)
            Application.DisplayAlerts = False
            .Delete
            Application.DisplayAlerts = True
        End With
    End If
    Dim ProposalReports As Workbook, CMCS As Variant
    Set ProposalReports = ThisWorkbook
    CMCS = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    If CMCS <> False Then
        Workbooks.Open (CMCS)
        Set CMCS = ActiveWorkbook
        Sheets("CMCS Bill of Materials").Select
        Sheets("Material Ad Hoc").Copy Before:=ThisWorkbook.Sheets(2)
    End If
End Sub

 

[Fluff]

How about

    If Evaluate("isref('Material Ad Hoc'!A1)") Then
      Sheets("Material Ad Hoc").Select
      Sheets("Material Ad Hoc").Copy After:=Sheets(9)
      Sheets("Material Ad Hoc").Select
      ActiveWindow.SelectedSheets.Delete
    End If