Help with Macros to scan codes from another file/workbook or sheet

[synges]

First of all let me start by saying that I have very limited experience with macros and VBA but I programmed in Java so I know the concepts of OOP

So we have a code system compromised of 10 digits
the first two digits are the main category of an item and then every next two digits are subcategories of the previous digits

I have an excel file with all the codes in column A and then next to each code is 5 columns with each of the category names starting with the main category going threw to the last subcategory

the data files we receive only have these codes and the data next to them so my manager wants me to program a macro that reads the item code and goes into the other excel file and gets the 5 columns with the category and subcategory names and then insert them after the code and before the data in the original file

Can this be done? can a macro goes and check another file? or do I have to copy the codes into the same file as the data and the macro then can check another worksheet?
if anybody can help with the major syntax and functions needed to achieve this I would be highly grateful.

 

[synges]

If the incoming sheet always has a header row and the header name for the code column is always the same, I could work with that to modify the code I posted and make it work for you.

it does! it's always called HS code! Thank you so much!
a couple of quick question
will you still make the macro work from the reference file and scanned the HS code column in the receiving file? or work make it that I load it in the receiving file and it scans the reference file?
also will the files need to be in the same directory for the macro to work?

 

[JLGWhiz]

This will still need some modifycation by you as noted in the comments. If the incoming file name varies, then you can just leave the wb2 workbook name as is and make sure the other workbook is opened first.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, wb1 As Workbook, wb2 As Workbook, cl As Long
Set wb1 = ThisWorkbook 'Assumes code will run from workbook containg category descriptions
Set wb2 = Workbooks(2) 'Need to substitute name of file from other source, edit name
Set sh1 = wb1.Sheets("Code") 'Sheet with category description, edit name
Set sh2 = wb2.Sheets(1) 'Sheet recieved from other sources, edit name
On Error Resume Next
cl = sh2.Rows(1).Find("HS Code", , xlValues).Column
On Error GoTo 0
Err.Clear
    If Not IsEmpty(cl) Then
        For Each c In Intersect(sh2.UsedRange.Offset(1), sh2.Columns(cl))
            If c <> "" Then
                Set fn = sh1.Range("A:A").Find(c.Value, , xlValues)
                    If Not fn Is Nothing Then
                        c.Offset(, 1).Resize(, 5).Insert xlShiftToRight
                        fn.Offset(, 1).Resize(, 5).Copy c.Offset(, 1)
                    End If
            End If
        Next
    End If
End Sub