loop through files in a folder based on partial name from worksheet cell

I need a macro to open all files in a folder that have the value in a cell somewhere in their filename. The files will be opened and closed one by one to copy some info and paste into the active workbook. Here's something to give you an idea what I'm after:

Sub test2()

Dim bk As Workbook
   Dim Criteria1 As String
    Dim Criteria2 As String
    Dim Criteria3 As String
    Dim Criteria As String
     Dim path As String
  
  path = ("C:\Documents and Settings\" & Environ("username") & "\Desktop\fiscal reports")
    
    Criteria1 = "*" & Sheets("console").Range("E8").Value & " " & Sheets("console").Range("F4").Value & "*" 'for all rep, entire program year
     Criteria2 = Sheets("console").Range("E8").Value & "*" & Sheets("console").Range("F4").Value & "*" 'for all agency, program entire year
     Criteria3 = "*" & Sheets("console").Range("F4").Value & "*" 'for all reports (all agency and all rep), entire program year (all reps or all agencies)
     
    If Sheets("console").Range("E6").Value = "Agency" Then
    Criteria = Criteria2
     End If
    
    If Sheets("console").Range("E6").Value = "Representative" Then
    Criteria = Criteria1
     End If
    
    If Sheets("console").Range("E6").Value = "All*" Then
    Criteria = Criteria3
     End If
 
'loop through all the books in the folder that meet the name criteria above
For Each bk In path
If bk.Name Like Criteria Then
bk.Open

'perform some actions, then close the workbook and open the next one

Activeworkbook.Close
Exit For
End If
Next

End Sub

Thanks yudlugar, do I need to set 'fil' to anything?

It should assign it to the files in the folder (the line 'for each fil in fol.files' does this) and loop through them. You do not need to define it outside of the loop.

Hmm, i can tell it's close but it actually doesn't open anything, and then it closes the "console" workbook.

How about:

Option Explicit

Sub Macro_1()

Dim fs, fol, fil, wb1, wb2, criteria

Set wb1 = ActiveWorkbook

If wb1.Sheets("console").Range("E6").Value = "Agency" Then
    criteria = "*" & Sheets("console").Range("E8").Value & " " & Sheets("console").Range("F4").Value & "*" 'for all rep, entire program year
ElseIf wb1.Sheets("console").Range("E6").Value = "Representative" Then
    criteria = Sheets("console").Range("E8").Value & "*" & Sheets("console").Range("F4").Value & "*" 'for all agency, program entire year
ElseIf Sheets("console").Range("E6").Value = "All*" Then
    criteria = "*" & Sheets("console").Range("F4").Value & "*" 'for all reports (all agency and all rep), entire program year (all reps or all agencies)
End If

Set fs = CreateObject("Scripting.filesystemobject")
Set fol = fs.getfolder("C:\Documents and Settings\" & Environ("username") & "\Desktop\fiscal reports")
For Each fil In fol.Files
    If fil.name like criteria then
        Set wb2 = Workbooks.Open(fil)
            'code to copy and paste info
            'eg wb2.Sheets(1).Range("A1:B5").copy wb1.sheets(1).Range("A1")
        wb2.Close
    End If
Next

End Sub

Thanks yudlugar! the first one actually worked, I just had to adjust my 'criteria' values.

Best regards,

Willardio