Code Error Run Time erro 13

[gheyman]

Type Mismatch

I'm getting the error on "If Not Evaluate...."

Sub Get_CMOP()
 Application.ScreenUpdating = False
    
    'Dim Consolidate As Workbook
    Dim SWEEP As Workbook
    'Dim CMOP As Workbook
    Dim CMOP As Workbook
    'Dim xLRw As Long
    'Dim xCMOPK As Long

    
    Set SWEEP = ThisWorkbook

    CMOPK = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")

    
    If CMOPK <> False Then
        Workbooks.Open (CMOPK)
        Set CMOP = ActiveWorkbook
        
            If Not Evaluate("isref(2017 CMOPK!a1)") Then
                MsgBox "The Selected Workbook does not contain a 54_TPL_01_02 tab"
                CMOP.Close False
                Exit Sub
            Else
                ActiveWorkbook.Sheets("2017 CMOPK").Copy SWEEP.Sheets(SWEEP.Sheets.Count)
        
                CMOP.Close False
            End If
            
    Else

    End If
        
    Application.ScreenUpdating = True


End Sub

I want to send up a message box, close a workbook and end the sub if a certain tab is not there.

THNAKS AGAIN!

 

[mikerickson]

You could try

If CMOPK <> False Then
    Workbooks.Open (CMOPK)
    Set CMOP = ActiveWorkbook
    On Error Goto Halt
    ActiveWorkbook.Sheets("2017 CMOPK").Copy SWEEP.Sheets(SWEEP.Sheets.Count)
        


Halt:
CMOP.Close False
On Error Goto 0

End Sub

 

[Fluff]

If the sheet name contains spaces you have to wrap it in single quotes ' like

isref('2017 CMOPK'!a1)

 

[gheyman]

Thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback