Greetings and thank you in advance for any help provided. I am having problems getting the code below to follow my intentions. I have 2 files. I have a temporary file that holds all the data that I need. I have a destination file that needs to have data copied into it. In the temp file, I have data for several dates and product types. What I intend to do is to do a vlookup in VBA to look for the date and the product type in the temp file and copy the appropriate data to the destination file. I have numerous named ranges both in my temp file and my destination file. For the code below, I wanted to make a loop to find the date in the temp file that is listed in the destination file. Once this is done, I wanted to find the product type in the temp file that is listed in the destination file. If both conditions exist, then copy a certain range from the temp file to the destination file. My intention is evident in the code, but I don’t think that I am putting in the correct “code format”. Can any one help?
Thank you
Public Sub MacroOption2()

    Dim sh As Worksheet
    Dim Day As String
    Dim rFoundIt As Range
    Dim iLoop As Integer

Application.ScreenUpdating = False

    ThisFile = ThisWorkbook.Name
    PathName1 = Sheets("SUMMARY").Range("TempPath1").Value
    Filename1 = Sheets("SUMMARY").Range("TempFile1").Value
    DestPath1 = Sheets("SUMMARY").Range("DestPath1").Value
    DestFile1 = Sheets("SUMMARY").Range("DestFile1").Value
    Date1 = Sheets("BGE Template Options").Range("Date1").Value
    ProductPRL = Sheets("GE Template Options").Range("ProductPL").Value
    ProductPRX = Sheets("GE Template Options").Range("ProductPX").Value
    
    Day = CStr(Sheets("SUMMARY").Range("DayValGE").Value)

 Workbooks.Open Filename:=PathName1 & Filename1
    With Sheets("BE").Range("Data")
        'Set variable to start search from
            Set rFoundIt = Cells(1, 1)
                'Loop no more times than the date occurs
                    For iLoop = 1 To WorksheetFuntion.CountIf_
                        Sheets("BE").Range ("Data"), .Range("Date1").Value
                        
        'Reset variable to found occurence of date to start next loop search
        'next loop search will start AFTER this Set cell
        Set rFountIt = .Find(What:= .Range("Date1").Value, After:=rFountIt,_
        LookIn:=xlValues,LookAt:=xlWhole,SearchOrder:=xlByRows,_
        SearchDirection:=xlNext,MatchCase:=False)
        
        'Check for product type after finding correct date
        If rFoundIt.Offset(1, 0).Value = .Range("ProductPL").Value Then
            Range("E5:E28").Select
                Selection.Copy
            
        Windows(ThisFile).Activate
        Set sh = Worksheets(Day)
            sh.Range("H7:H30").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
       
       Windows(Filename1).Activate
       
    Next iLoop
    End With
                
    Windows(Filename1).Activate
        ActiveWorkbook.Save
        ActiveWindow.Close
End Sub