Okay done some more testing. From my very limited knowledge what seems to be
happening is that the find command does not activate each sheet when it
searches through it.
Any idea how I activate the correct sheet based on the macro below

"Craig McK" wrote:

Hi Bob

Thanks for that, but having a similar problem with this one. As follows:-
If the cell is in the worksheet that was saved it works fine
If the cell is in another worksheet the macro fails at that line.

Just to confirm using Excel 2003 in case the VBA changed.

Any thoughts

"Bob Phillips" wrote:

I don't replicate the problem, but try changing

Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate

to

sh.Range(rng.Address).Offset(rowoffset:=0,
columnoffset:=1).Activate


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Craig McK" wrote in message
...
HI
Hope that someone can help. The following looks up a field in workbook 1,
then searches workbook 2 for that field. Then returns the price from the
adjacent cell back to workbook 1.

My problem is that while it does find the correct sheet/cell. When it
ruturns that value is uses the correct cell ref, but selects from the
sheet
that the workbook was last saved from, not the sheet the item was found
on.

Can anyone advise what I need to do to get it to select from the correct
sheet.


The plan is that it will finally run through the complete item list in
workbook 1, but just now it is set to look at cell A5 only


Thanks

-------------------------------------------
Sub Findinworkbook()
Dim rng As Range, sh As Worksheet
Dim vTarget As Variant, fAddr As String, sAddr As String, fDesc As String

Dim lookfor As String
Dim selectcell As String

sAddr = "A5"

lookfor = Range(sAddr).Value

Workbooks.Open ("workbook 2")


vTarget = lookfor
For Each sh In ActiveWorkbook.Worksheets


With sh.Cells
Set rng = .Find(vTarget, LookIn:=xlValues, lookat:=xlWhole,
searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
fAddr = rng.Address



Do

Range(rng.Address).Select
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate
Selection.Copy
Workbooks("workbook 1").Activate
Range(sAddr).Select
ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Activate
ActiveSheet.Paste
Set rng = .FindNext(rng)
Loop While rng.Address < fAddr
End If
End With
Next sh

End Sub