Pulling info from one tab to another

[edwar81]

Hi everyone!
To make it easy, I'll explain my issue now: I'm building a parts database on one tab in excel. Everything my company sells is going to be entered there...can I set up on another tab that when I enter a part number in Column A on Sheet 1, it finds it on Column A of Sheet 2 and will put the part description from Sheet 2, Column B on Column B of Sheet 1?

Hope I made sense with that!

Thanks in advance for your help!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column A of Sheet1. This assumes your data is in Sheet2, columns A and B. Private

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundPart As Range
    Set foundPart = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundPart Is Nothing Then
        Target.Offset(0, 1) = foundPart.Offset(0, 1)
    Else
        MsgBox ("Part not found.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[edwar81]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column A of Sheet1. This assumes your data is in Sheet2, columns A and B. Private

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundPart As Range
    Set foundPart = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundPart Is Nothing Then
        Target.Offset(0, 1) = foundPart.Offset(0, 1)
    Else
        MsgBox ("Part not found.")
    End If
    Application.ScreenUpdating = True
End Sub

I tried that, but it gives me a "Run-time error '9': Subscript out of range" Message...

 

[edwar81]

I just got it to work without the error, but it isn't pulling the info from column 2 of sheet 2 into column 2 of sheet 1...any ideas why that could be?

 

[mumps]

When I tried it on some dummy data it worked properly. It would be easier to check for problems if I could work with your actual file. Perhaps you could upload your file to a free site such as www.box.com or www.dropbox.com. After you upload the file, mark it for 'Sharing' and you'll be given a link to the file that you can post here.

 

[edwar81]

I got it all working now, I had to play with where it was pulling the info from, but it's actually working better than I had imagined!!!

Thanks for all your help and patience!!!

 

[mumps]

You are very welcome.

 

[miless2111s]

Or use a vlookup?