Macro question

[fredlef]

I am trying to record a macro that copies from one sheet and pastes info to another sheet based on a find. When i look at the vbe it shows:

Cells.Find(What:="100* Total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A27:M27").Select
Range("M27").Activate

As you can see the problem is that it always copies a "hard" range as opposed to the next appropriate range. Can anyone tell me how to fix this? Thanks

[Ivan F Moala]

Something along these lines may help;

Sub FindCopy()
Dim Found As Range

Set Found = Cells.Find(What:="100 TOTAL", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Found Is Nothing Then MsgBox "Not found!": Exit Sub
Found.Copy Sheets("Sheet2").Range("A1")

End Sub

'Notes copies the cell to Sheet2 A1
'change as required

Ivan