I need help please, i am trying to get my copyRange to only copy the
1st 5 columns of the row
that it has found by using the .Find (FindFirst and FindLast)

With ActiveSheet
Set searchRange = .Range("F2:F" & FinalRow)
With searchRange
Set FindFirst = .Find(what:=WhatToFind, _
LookIn:=xlValues, lookat:=xlWhole, _
after:=.Cells(.Cells.Count), _
searchdirection:=xlNext)
Set FindLast = .Find(what:=WhatToFind, _
LookIn:=xlValues, lookat:=xlWhole, _
after:=.Cells(1),
searchdirection:=xlPrevious)
'Below is where i need the help....
Set copyRange = .Range(FindFirst, FindLast).EntireRow
End With
End With

'with out the .EntireRow
? copyrange.Address
$F$9:$F$182
'with the .EntireRow
? copyrange.Address
$9$182

Thanks for any help that is offered.

Keri~

here is the rest of the code...
Sub SendData()

Dim FindFirst As Range
Dim FindLast As Range
Dim searchRange As Range
Dim copyRange As Range
Dim WhatToFind As String
Dim destCell As Range
Dim FinalRow As Long
Dim sBook As Workbook
Dim sSheet As Worksheet
Dim dBook As Workbook
Dim dSheet As Worksheet
Dim strPri, strSec, strNon As String
Dim Primary, Secondary, NonProduction As String
Dim theName As String
Primary = "Primary"
Secondary = "Secondary"
NonProduction = "Non-Production"
FinalRow = Cells(Rows.Count, 6).End(xlUp).Row

'at some point i want it to loop though (Pimary,secondary and
nonproduction) in the .Find
'wanted to start with one, so i can understand it better
WhatToFind = "Primary"

Application.DisplayAlerts = False

Set sBook = ThisWorkbook 'or ActiveWorkbook ???
Set dBook = Workbooks.Open("E:\Excel\Portlet & Global Dev together
TEST\DesBook.xls")
Set dSheet = dBook.Sheets("Template")


For Each sSheet In sBook.Worksheets
sSheet.Activate
With ActiveSheet
Set searchRange = .Range("F2:F" & FinalRow)
With searchRange
Set FindFirst = .Find(what:=WhatToFind, _
LookIn:=xlValues, lookat:=xlWhole, _
after:=.Cells(.Cells.Count), _
searchdirection:=xlNext)
Set FindLast = .Find(what:=WhatToFind, _
LookIn:=xlValues, lookat:=xlWhole, _
after:=.Cells(1),
searchdirection:=xlPrevious)
'I need the help below
Set copyRange = .Range(FindFirst, FindLast)
End With
End With
If FindFirst Is Nothing Then
MsgBox "Nothing found"
Exit Sub
End If


theName = sSheet.Name

With dBook.Worksheets
dSheet.Copy after:=.Item(.Count)
ActiveSheet.Name = theName
Set destCell = Range("A8")
End With

copyRange.Copy Destination:=destCell

Next sSheet


End Sub

I figured out this one, but have other questions i made about loops in
another post.

K~