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.

 

[JLGWhiz]

Since there was not sufficient detail in the OP, the code below will need some user modification in order to work properly. Comments are added in the code to show where modifications are needed. It was assumed that the ten digit code would be in column A of the worksheet in each file. If not that also needs to be modified in the code.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, wb1 As Workbook, wb2 As Workbook
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("Incoming") 'Sheet recieved from other sources, edit name
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        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
    Next
End Sub

 

[synges]

Since there was not sufficient detail in the OP, the code below will need some user modification in order to work properly. Comments are added in the code to show where modifications are needed. It was assumed that the ten digit code would be in column A of the worksheet in each file. If not that also needs to be modified in the code.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, wb1 As Workbook, wb2 As Workbook
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("Incoming") 'Sheet recieved from other sources, edit name
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        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
    Next
End Sub

Thanks for your reply sorry if my original post wasn't clear enough
I have attached a sample image showing how the reference file with the codes and description is laid out
the file name doesn't change and the layout is always the same
the codes are in the first column and have several description based on each pair of digits

the file that we receive that have the data have one of the columns that have the item code (can be any column) and several other columns of data
the goal like I said is to use the item code column from the data file to look at the reference file and grab the 5 descriptions and put them next to the corresponding item code in the data file

vlookup was one function that I tough off but it gets only one value of a single cell and it needs to be repeated 5 times and I also need to open the reference file and specify each time the column we are searching
so I thought I need a macro where I run it on the data file received where I specify what column in the data file has the item codes
then the macro proceeds to open the reference file match the code with an item code from the first column
grabs the 5 descriptions of the item and then inserts them in 5 columns next to the item code in the data file and of course this repeats for all data rows in the data file

 

[Fluff]

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

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[synges]

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

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

sorry i will edit my post and mention it

 

[synges]

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

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

sorry but I can't find an option to edit the post

 

[Fluff]

No need to edit the post as I have already supplied the link.
Just please remember to add any links in future, should you cross post again.

 

[JLGWhiz]

The resoponders on the other forum are having the same problem I have. Without knowing whrere on the incoming sheet the code might be found, it would require that each code on your reference sheet be sarched in every cell of the incoming sheet to find a match. I don't want to write that kind of code, for one reason it is open to producing undesired results and creating problems that would be difficult to solve from here. If you cannot provide some kind of image to the incoming sheet and determine what is constantt about it, then I will just drop off this thread and let the other responders work it.

 

[synges]

The resoponders on the other forum are having the same problem I have. Without knowing whrere on the incoming sheet the code might be found, it would require that each code on your reference sheet be sarched in every cell of the incoming sheet to find a match. I don't want to write that kind of code, for one reason it is open to producing undesired results and creating problems that would be difficult to solve from here. If you cannot provide some kind of image to the incoming sheet and determine what is constantt about it, then I will just drop off this thread and let the other responders work it.

the problem is that the column of codes in the incoming sheets isn't always consistent
it might be the first column or it might be the 5th column for instance
I can provide a sample when I get back to work on Monday
but my idea was when I get the incoming sheet I would specify to the macro the column that contain the codes
and the the macro proceeds to take the first code in the column and goes and search with it in Column A of the reference sheet that contains the codes there
then extract the values next to it from the reference sheet and then inserts it next to the column containing the code in the incoming sheet (the column I specified at the start)
then it goes and repeats that for the next row (or at least do it for 1 row and I can then repeat the macro for the rows I want)

I hope that makes it a bit clearer and again thanks for your reply and patience

 

[JLGWhiz]

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.