I am trying to select rows that only include the totals...the row includes an
acct #, a sub and the word Total...so i am using mid to find the word total
in each row, then put the acct number in a separate sheet. For example here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy 11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula

If you have a lot of these AND total is part of the string in col D then use
this to get
11002 103.88
11003 194.79

Sub copytotalrowinfo()
dr = 2
For i = 2 To Cells(Rows.Count, "d").End(xlUp).Row
If InStr(Cells(i, "d"), "Total") Then
With Sheets("destinationsheetname")
.Cells(dr, 1) = Left(Cells(i, "d"), _
InStr(Cells(i, "d"), "-") - 1)
.Cells(dr, 2) = Cells(i, "L")
End With
dr = dr + 1
End If
Next i

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
I am trying to select rows that only include the totals...the row includes
an
acct #, a sub and the word Total...so i am using mid to find the word
total
in each row, then put the acct number in a separate sheet. For example
here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy
11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula

If you want to try with functions then try this one

use helping column and place formula & drag it down.
Then filter with "Total"

=IF(ISERROR(FIND("total",D1)),"","Total")



On Jun 6, 8:38*pm, pm wrote:
I am trying to select rows that only include the totals...the row includes an
acct #, a sub and the word Total...so i am using mid to find the word total
in each row, then put the acct number in a separate sheet. *For example here
are 3 rows from one of my sheets:
column D * * * * * * * * * column L
11002-01256 * * * * * * * 41.11
11002-01256 * * * * * * * 62.76
11002-00707 Total * * *103.88

So i only want to select the last row, since it is the total and copy 11002
to the new sheet; AND i want to include the amount on my new sheet. *

Set colDRange = ws.Range("D1:" & _
* * * *ws.Range("D" & Rows.Count).End(xlUp).Address)
* * * For Each anyColDCell In colDRange
* * * * If Mid(anyColDCell, 13, 5) = "Total" Then
* * * * * *newFormula = "=MID('" & ws.Name & _
* * * * * *"'!R[3]C[3],1,5)"
* * * * *echoSheet.Range("A" & Rows.Count). _
* * * * * *End(xlUp).Offset(1, 0).FormulaR1C1 _
* * * * * *= newFormula

"Don Guillett" wrote:

If you have a lot of these AND total is part of the string in col D then use
this to get
11002 103.88
11003 194.79

Sub copytotalrowinfo()
dr = 2
For i = 2 To Cells(Rows.Count, "d").End(xlUp).Row
If InStr(Cells(i, "d"), "Total") Then
With Sheets("destinationsheetname")
.Cells(dr, 1) = Left(Cells(i, "d"), _
InStr(Cells(i, "d"), "-") - 1)
.Cells(dr, 2) = Cells(i, "L")
End With
dr = dr + 1
End If
Next i

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
I am trying to select rows that only include the totals...the row includes
an
acct #, a sub and the word Total...so i am using mid to find the word
total
in each row, then put the acct number in a separate sheet. For example
here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy
11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula



Hi Don,

I have 5 sheets to read.....the total amount is not included in the string
in column D. the amount is in a separate column (L).

When replying to me, please do so at the TOP. Your original post was not
clear and you still don't tell us where "Total" is (col E perhaps?) or where
you want it to go????. If desired, send your file to my address below with
this msg and I'll take a look.

I have 5 sheets to read.....the total amount is not included in the string
in column D. the amount is in a separate column (L).

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...


"Don Guillett" wrote:

If you have a lot of these AND total is part of the string in col D then
use
this to get
11002 103.88
11003 194.79

Sub copytotalrowinfo()
dr = 2
For i = 2 To Cells(Rows.Count, "d").End(xlUp).Row
If InStr(Cells(i, "d"), "Total") Then
With Sheets("destinationsheetname")
.Cells(dr, 1) = Left(Cells(i, "d"), _
InStr(Cells(i, "d"), "-") - 1)
.Cells(dr, 2) = Cells(i, "L")
End With
dr = dr + 1
End If
Next i

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
I am trying to select rows that only include the totals...the row
includes
an
acct #, a sub and the word Total...so i am using mid to find the word
total
in each row, then put the acct number in a separate sheet. For example
here
are 3 rows from one of my sheets:
column D column L
11002-01256 41.11
11002-01256 62.76
11002-00707 Total 103.88

So i only want to select the last row, since it is the total and copy
11002
to the new sheet; AND i want to include the amount on my new sheet.


Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Mid(anyColDCell, 13, 5) = "Total" Then
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula



Hi Don,

I have 5 sheets to read.....the total amount is not included in the string
in column D. the amount is in a separate column (L).