Select, find in another book, copy, return and paste

[Watson]

Excel 2003

I have multiple catalogue workbooks, all use the same layout. I want to
update the on hand stock in column H for the item numbers I select in
column B. All on hands are in workbook peter1.xls in column G.

I have recorded the steps I would like to do with the macro recorder but
I want this to work with any cell or range I select in B from any of the
workbooks I open.


Sub Find()
'
' Find Macro
' Macro recorded 15/08/2008
'

'
Windows("Beverageware 111 Basic Decorated.xls").Activate
Selection.Copy
Windows("peter1.xls").Activate
Cells.Find(What:="P221558", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("G1991").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Beverageware 111 Basic Decorated.xls").Activate
Range("H8").Select
ActiveSheet.Paste
End Sub




I hope this explains what I need.

 

[Duke Carey]

No tested. Try

Sub Find()
dim ws as worksheet
dim rngFound as range

' change next line to reflect the correct worksheet
set ws = Windows("peter1.xls").worksheets(1)

set rngFound = ws.find(What:=activecell.value, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if not rngFound is nothing then
' the next line grabs the value to the right of the found value and puts it
next to the activecell - change as needed
activecell.offset(0,1) = rngfound.offset(0,1)
end if
End Sub

 

[Duke Carey]

Found an error. Try this instead

Sub Find()
dim ws as worksheet
dim rngFound as range

' change next line to reflect the correct worksheet
set ws = Windows("peter1.xls").worksheets(1)

set rngFound = ws.cells.find(What:=activecell.value, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if not rngFound is nothing then
' the next line grabs the value to the right of the found value and puts it
next to the activecell - change as needed
activecell.offset(0,1) = rngfound.offset(0,1)
end if
End Sub

 

[Watson]

Sub Find()
dim ws as worksheet
dim rngFound as range

' change next line to reflect the correct worksheet
set ws = Windows("peter1.xls").worksheets(1)

set rngFound = ws.cells.find(What:=activecell.value,
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _ MatchCase:=False,
SearchFormat:=False)

if not rngFound is nothing then
' the next line grabs the value to the right of the found value and
puts it next to the activecell - change as needed
activecell.offset(0,1) = rngfound.offset(0,1)
end if
End Sub

Thanks,

I tried this but I get a compile error syntax on
set rngFound = ws.cells.find(What:=activecell.value,


Also do I change both offset values to 0,5 if I want to copy the cell in row G?