I don't like relying on the activesheet of a different workbook.

If you know the name of the worksheet in toys.xls, you might as well specify it:

Dim FoundCell As Range
Dim ToysWks As Worksheet

Set ToysWks = Workbooks("Toys.xls").Worksheets("somesheetnameher e")

Set FoundCell = Cells.Find(What:="Toys", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If FoundCell Is Nothing Then End
'not found, do nothing or whatever you want
MsgBox "Toys not found!"
Else
ToysWks.Range("a23").Value = FoundCell.End(xlToRight).Value
End With



carl wrote:

Hi Dave,

Thanks for your help. Being a bit of a beginner I can't get this to work.
I changed it because I assume I couldn't just copy and paste it in to my
script. Either way it's not worked. I changed it to this:

With Cells.Find(What:="Toys", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim FoundCell As Range
Set FoundCell = Cells.Find(Toys) 'no .activate here!
If FoundCell Is Nothing Then End
'not found, do nothing or whatever you want
Else
Selection.End(xlToRight).Select
ActiveCell.Select
Selection.Copy
Windows("Toys.xls").Activate
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues
'found it, do the real work.
End With

It stops at the "Else" part saying that it needs "If" after "Else". Am I
doing this all wrong?

"Dave Peterson" wrote:

Dim FoundCell as range

set foundcell = cells.find(....) 'no .activate here!

if foundcell is nothing then
'not found, do nothing or whatever you want
else
'found it, do the real work.
end if


Dave Peterson

--

Dave Peterson