looping code

DATA SHEETS folder as Directory.xls
is the lookup table for reference

Books1 is a modified version of the whole bill of materials so that it could be small enough to post here.

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.

Thanks so much, I'm trying to wrap my head around that now
This is kind of a description of what i'm trying for:

All blocks in my AutoCAD documents have been embedded with attributes that reflect each part: my inventory number, the manufacturer, a description, and so on. This information goes into an excel spreadsheet, it’s dumped onto one sheet and 3 other sheets take that information and automatically update the other pages for a bill of materials, fuse table etc. (That's what book1 shows). From this information I need to manually collect cut sheets for each part in the project from a data base of cut sheets. What I’m trying to do, is run the macro from the sheet “Bill of materials” when there is a count for an item, I want it to be seen by the macro and go out and copy that associated cut sheet to the file the .xls is located.