one way:

Public Sub TransferTotals()
Dim rFound As Range
Dim rDest As Range
Dim sFoundAddr As String

Set rDest = Sheets("Sheet3").Range("A2")
Set rFound = Columns(8).Find( _
What:="Total", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=xlFalse)
If Not rFound Is Nothing Then
sFoundAddr = rFound.Address
Do
rDest.Offset(0, 1).Value = rFound.Offset(0,1).Value
rDest.Value = _
rFound.Offset(-2, -6).Value
Set rFound = Columns(8).FindNext( _
After:=rFound)
Set rDest = rDest.Offset(1, 0)
Loop Until rFound.Address = sFoundAddr
End If
End Sub





Columns(8).FindNext(In article
,
"RJH" wrote:

Hello,
I have detailed orders listed on page 2. I would like to scan the page and
find each occurrence of the word "Total" (which is in column 'H'), once
finding that, I would like to copy each number associated with "Total"
(numbers are in 'I') and the order number associated with the order (2 rows
up and in column 'B'). I would then like to paste the 2 items side by side
to page 3 starting at A2. (Order # in A2, Total in B2).

The best code I could come up with would only copy a single occurrence of
the 'word' Total to page 3.

I appreciate your help!

Thanks!

Bob Howard