list files which do not contain sheet: TEST

[Rookeby7]

I have used this to list files in folder,
but now want to only list files with do not contain a worksheet "TEST" can anyone help please?

To go one step further I would move the files which do not have worksheet TEST to a new subfolder "NO TEST"

Thanks in advance

***********
Sub List_Files()
Dim MyFolder As String
Dim myFile As String
Dim a As Integer

MyFolder = "C:\CHECK"
myFile = Dir(MyFolder & "*.*")
a = 0
Do While myFile <> ""
a = a + 1
Cells(a, 1).Value = myFile
myFile = Dir
Loop

End Sub

 

[DanteAmor]

Try this

Change the folder names for yours
.

Sub List_Files()
  Dim MyFolder As String, myFile As String, a As Integer, MyFolderDes As String
  Dim wb As Workbook, sh As Worksheet, notest As Boolean, fso As Object
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  MyFolder = "[COLOR=#ff0000]C:\CHECK\[/COLOR]"
  MyFolderDes = "[COLOR=#ff0000]C:\CHECK\NO TEST\[/COLOR]"
  
  Set fso = CreateObject("scripting.filesystemobject")
  myFile = Dir(MyFolder & "*.xls*")
  a = 1
  Do While myFile <> ""
    Set wb = Workbooks.Open(MyFolder & myFile)
    notest = True
    For Each sh In wb.Sheets
      If UCase(sh.Name) = UCase("TEST") Then
        notest = False
        Exit For
      End If
    Next
    wb.Close False
    If notest = True Then
      Cells(a, 1).Value = myFile
      a = a + 1
      fso.MoveFile Source:=MyFolder & myFile, Destination:=MyFolderDes & myFile
    End If
    myFile = Dir()
  Loop
  MsgBox "End"
End Sub