Macro that runs when a sheetname in a file does not exist

Hi Guys!

I am looking for a solution to a problem i'm facing in the code below I want to import data from a file if the file name contains "Test". If the filename contains "Test" VBA should unprotect the sheets and look for a sheet called "Check test".

This is where the problem starts. Not every file called "Test" has a sheet called check test. I would like the macro to move on to the next workbook (and preferably give a message) if this sheet does not exist in the file. Please help out!!

Sub Test_import()

Dim wb As Workbook
Dim wbName As String
Dim Password As String
Dim ws As Worksheet
Dim activewrkbook As Workbook
Dim lSecurity As Long

'Change this line if you want to activate workbooks with a different name that are opened
wbName = "test"
Password = "DUMMY"

Application.ScreenUpdating = False


Workbooks("Certificates file.3.xlsm").Activate

'Start of the For Each loop that checks all opened workbooks containing "wbName"
For Each wb In Application.Workbooks
   
    

                
            If LCase(wb.Name) Like "*" & wbName & "*" Then
                    Debug.Print wb.Name
                    wb.Activate
                                        
                Application.DisplayAlerts = False
            
                Set activewrkbook = ActiveWorkbook
                
            'When there's an error go to the bottom error message
            'On Error GoTo errormsg
       
                    'On Error Resume Next
                    ActiveWorkbook.Unprotect Password:=Password
                    
            'Protect each sheet in the active workbook
                For Each ws In ActiveWorkbook.Worksheets
                    
                    ws.Unprotect Password:=Password
                Next ws
                    
                
                activewrkbook.Activate
                             
'            If WorksheetExists2("Check sheet") Then
            
                    Sheets("Check Test").Activate
                    If Err <> 0 Then
            
            'Check if cell B2 contains the text "Niet geslaagd"
                    If activewrkbook.Sheets("Check test").Range("B2").Value = "Niet geslaagd" Then
                    
            
                        MsgBox "Participant did not pass the test. Please manually review the test."
                        
                        
                    
                    ElseIf activewrkbook.Sheets("Check test").Range("B2").Value = 1 Then
                        
                        Sheets("Check test").Range("B5:J5").Copy
                        
                        Workbooks("Certificates file.3.xlsm").Activate
                        Sheets("Input sheet").Activate
                            
                        Range("B2").Select
                        
                        If ActiveCell.Offset(1, 0).Value = "" Then
                        
                            ActiveCell.Offset(1, 0).Select
                            ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
                            
                            Range("A2").Select
                             
                        Else
                        
                            Selection.End(xlDown).Select
                            ActiveCell.Offset(1, 0).Select
                            
                            ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
                            
                            Range("A2").Select
                            
                            
                        End If
                    
            Else
                    
                    End If
                
                     'Closes the active workbook
            '        If activewrkbook.Name Like "*" & wbName & "*" And activewrkbook.Sheets("Check Test").Range("B2").Value <> "Niet geslaagd" Then
                   ' If activewrkbook.Name Like "*" & wbName & "*"
            
                    If LCase(activewrkbook.Name) Like "*" & wbName & "*" And activewrkbook.Sheets("Check Test").Range("B2").Value = 1 Then
                    
                        activewrkbook.Close
                    
                    
                    Else
                    
                    End If
                    
                End If

nxt:
Next wb


        MsgBox "Data has been imported"
         
        On Error Resume Next
        Workbooks("Certificates file").Worksheets("Input sheet").Activate
    
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
        Exit Sub
            

End Sub

Originally Posted by PCI

You have already a test done to check if the worksheet exit

If WorksheetExists2("Check sheet") Then

What do you need more ???

This doesn’t seem to work for Some reason...