Copy 2 items to next sheet
 

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

Copy 2 items to next sheet
 

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

Copy 2 items to next sheet
 

That worked great with only one hitch. I didn't explain my needs very well.
Sheet2 will be cleared from time to time and Sheet3 will continue on. I
need to post the totals from sheet2 to the last empty row on sheet3. I
tried working on your code to make it do this but, no luck. If you could
help again I would appreciate it.

Thanks!

Bob Howard

"JE McGimpsey" wrote in message
...
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

Copy 2 items to next sheet
 

One way:

Replace

Set rDest = Sheets("Sheet3").Range("A2")

with

Set rDest = Sheets("Sheet3").Range( _
"A" & Rows.Count).End(xlUp).Offset(1, 0)



In article
,
"RJH" wrote:

That worked great with only one hitch. I didn't explain my needs very well.
Sheet2 will be cleared from time to time and Sheet3 will continue on. I
need to post the totals from sheet2 to the last empty row on sheet3. I
tried working on your code to make it do this but, no luck. If you could