Maybe...

Option explicit
Public Sub FindTheYeLLeR()
Dim myCell As Range
dim wks as worksheet

with activeworkbook
set wks = .worksheets(.worksheets.count)
end with

For Each mycell In wks.Range("E244:E1000").cells
If mycell.Value = 1 Then
mycell.Offset(0, -2).Copy _
Destination:=mycell.Offset(0, 7)
End If
Next mycell
End Sub

Oke Doke wrote:

This Works just fine as written it will copy to the current sheet
just fine only I need it to Find the Last Sheet IN Workbook ("Master1")
and copy to Range("J1:J140") of that sheet every time i run this This
Macro The Sheet Name will Be different Thats why I need to have this
routine find the Name.
Im running windows xp sp2 with excel 2003
I will certanly be grateful to anyone that will bail me out as im
firmly stuck Thanks Boyd

Public Sub FindTheYeLLeR()
Dim r As Range
Dim i As Variant
i = i
i = i + 1
For Each i In Range("E244:E1000")
If i.Value = 1 Then
i.Offset(o, -2).Copy Destination:=i.Offset(0, 7)
'Exit For
End If
i = i + 1
Next i
End Sub

--

Dave Peterson