looping code

Here is where I am at so far. I'm not quite sure what else you want the macro to do...

Put this bit of code in a module named module1

Sub xferData()
    Windows("DATASHEETS").Activate
    'Sheets("Sheet1").Select
    Cells.Select
    Cells.Copy
    Windows("Book1").Activate
    Sheets.Add.Name = "data"
    Cells.Select
    ActiveSheet.Paste
End Sub

Now put the next bit of code in your workbook

Sub searchMyData()
    Dim inventoryRng As Range, inventory As Range
    Dim dataRng As Range, data As Range
    
    Call Module1.xferData
        
Sheets("Sheet1").Select
    With Sheets("Sheet1")
        Set inventoryRng = .Range(.Cells(1, 2), .Cells(Rows.Count, 2).End(xlUp))
            For Each inventory In inventoryRng
                If inventory <> vbNullString Then
                    If inventory.Offset(, 2) > 0 Then
                        With Sheets("data")
                            Set dataRng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
                               For Each data In dataRng
                                   If inventory = data Then
                                   file = data.Offset(, 2) & data.Offset(, 1)
                                       If Dir(file) <> "" Then
                                           MsgBox "File " & file & " exists!"
                                       Else
                                           MsgBox "File " & file & " does Not Exist"
                                       End If
                                   End If
                               Next data
                        End With
                    End If
                End If
            Next inventory
    End With
Application.DisplayAlerts = False
Sheets("data").Delete
Application.DisplayAlerts = True
End Sub

This should loop through everything for you. Explain a little bit further if you need help with anything more.