Find in another worksheet and copy to original worksheet.

[Reinie]

I have two worksheets with or without the same info in either of the sheets.
For each cell in worksheet "w SKUs" column "Package #" I need to find the
same "Package #" in another sheet "2009 data" and copy the "Suggested retail"
column's cell in the second sheet to the "Suggested retail" field in the
first sheet. The rows are not in the same order between the sheets.

It needs to handle not finding the "Package #" in the second sheet and go to
the next "Package #" cell in the first sheet, as well as handle the last
"Package #" in the first sheet.

I recorded a macro, but can't seem to handle the "not found" case. I've
programmed in C and assembly language for many years, but the OO paradigm
just blows me away. Even the object browser is mysterious as I can't tell
what should be a method, object, property, class, etc, and what to use where!

PReinie

Macro follows:
-------------------
Sub CopyFromOtherSheet()
'
' CopyFromOtherSheet Macro
' Find current cell in another worksheet and copy Sugg Retail from that
sheet to this one.
'
' Keyboard Shortcut: Ctrl+q
'

lookForThis = ActiveCell.Value
displayMsg = "The contents to find of the current ActiveCell is: " +
lookForThis + "."
' diplay_result = MsgBox(displayMsg, vbOKOnly)

Sheets("2009 data").Select

' 20090625: Changed to do the find first and get the result (variable) then
' perform the If using the variable, but there are still problems with it.
If Cells.Find(What:=lookForThis, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True _
, SearchFormat:=False).Activate Then
' ResultOfFind = Worksheet.Find(What:=lookForThis, After:=ActiveCell,
LookIn:=xlValues, LookAt:=
' xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True _
' , SearchFormat:=False)
' If ResultOfFind Then
' ResultOfFind.Activate
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.Copy
' Change the background color to show it was found and used...
With Selection.Interior
.Color = RGB(255, 230, 255)
.TintAndShade = 0
End With
ActiveCell.Offset(0, -3).Range("A1").Select
Sheets("w SKUs").Select
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
' Change the background color to show it was updated...
With Selection.Interior
.Color = RGB(255, 230, 255)
.TintAndShade = 0
End With
ActiveCell.Offset(1, -3).Range("A1").Select
Else
Sheets("w SKUs").Select
ActiveCell.Offset(1, 0).Range("A1").Select
End If
End Sub