How to identify a code in file name and open that file in a folder? The codes below don't work

Question

I am working with a lot of templates to update for this month. One of the tasks is to find the report for a specific template (located in another folder), open it, go to a tab, copy the content and paste to another tab in the template. The big issue is how to identify the report that goes with the template.

In cell A3 of each template, there is a BU code that is included in the file name of the report (but the file name is much longer than that - it's named as region_BU code_XXXXXXXXXXX, where the "X" can be any). I am trying to find the BU code in the file name and open that file. The codes below actually opened one file, but nothing was copied and pasted. So I was not sure what happened... I am a newbie so any help will be much appreciated!

Sub Macro1()
    Set fso = CreateObject("scripting.filesystemobject")
    Set ff = fso.getfolder("C:\Users\Win_1\summary\test")
    
    For Each file In ff.Files
    
        Workbooks.Open file
        Set wbk2 = ActiveWorkbook
        Sheets("Summary").Select
    
        rngY = Range("A3").Value
    
        Dim fname As Variant
        Dim myFile As String
    
        myPath = "C:\Users\Win_1\MLA\reports"
        fname = Dir(myPath & "*rngY*")
    
        If fname <> "" Then

            Workbooks.Open (myPath & fname)
            Set wbk1 = ActiveWorkbook
            Sheets("Assumptions Report").Cells.Select
            Selection.Copy
            wbk2.Activate
            Sheets("3-22").Select
            Range("A1").Select
            ActiveCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            wbk1.Activate
            Sheets("New Report").Range("D10").Select
            Selection.Copy
            wbk2.Activate
            Sheets("Summary").Select
            Dim rFound As Range
            Set rFound = Range("A10:A100").Find(Format("44651", "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
            
            If Not rFound Is Nothing Then rFound.Select
    
            ActiveCell.Offset(0, 3).PasteSpecial Paste:=xlPasteValues
            wbk1.Activate
            ActiveWorkbook.Save
            ActiveWorkbook.Close

        End If

        wbk2.Activate
        Range("A1").Select
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    
    Next
    
End Sub

Answer 1

I've made a couple of tweaks to your code, below (See the comments)

Sub Macro1()
    Set fso = CreateObject("scripting.filesystemobject")
    Set ff = fso.getfolder("C:\Users\Win_1\summary\test")
    
    For Each file In ff.Files
        Set wbk2 = Workbooks.Open(file) 'this is better than relying on Activeworkbook once you've opened the file.
        wbk2.Sheets("Summary").Select
    
        rngY = Range("A3").Value
    
        Dim fname As Variant
        Dim myFile As String
    
        myPath = "C:\Users\Win_1\MLA\reports"
        fname = Dir(myPath & "*" & rngY& "*")
    
        If fname <> "" Then

            Set wbk1 =  Workbooks.Open(myPath & fname)
            wbk1.Sheets("Assumptions Report").Cells.Copy 'avoid using Select wherever possible.
            wbk2.Activate
            Sheets("3-22").Activate
            Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            wbk1.Activate
            Sheets("New Report").Range("D10").Copy
            wbk2.Activate
            Sheets("Summary").Activate
            Dim rFound As Range
            Set rFound = Range("A10:A100").Find(Format("44651", "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
            
            If Not rFound Is Nothing Then 
                  rFound.Offset(0, 3).PasteSpecial Paste:=xlPasteValues
            End If
            wbk1.Save
            wbk1.Close

        End If

        wbk2.Save
        wbk2.Close
    
    Next
    
End Sub