Hi Juan

Here is a example for the range A1:B100
with in column A the test ???total

I use Str = "*Total"
It will filter on each row with Total as the last word in A1:A100


Sub Copy_With_AutoFilter()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim Str As String

Set WS = Sheets("sheet1")
Str = "*Total"

With WS.Range("A1:B100")
'Use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'This example filter on the first column in the range (change the field if needed)
.AutoFilter Field:=1, Criteria1:=Str
Set WSNew = Sheets.Add
.Cells.SpecialCells(xlCellTypeVisible).Copy WSNew.Range("A1")
End With

WS.AutoFilterMode = False
On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Juan" wrote in message ...
Hello Ron,
sorry for late respond. The thing is that I have
subtotals. So I want to copy just the subtotals to another
sheet. So need to find all rows with the word Total and
copy to another sheet.
Please advise if anythign else I can do.

Thanks,

Juan
-----Original Message-----
Hi Juan

Filter your column
http://www.rondebruin.nl/copy5.htm

Change the range and sheet name in the code



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Juan" wrote in message
...
Hello Ben,
thanks but not working. I copied code and when I run get
message saying Object required and it highllights the
For m = 1 To actsh.UsedRange.Rows.Count line
So not sure if you have any other ideas?

appreciate any other help.

thanks,

juan
-----Original Message-----

sub runme()
set actsh = activesheet
set shcopyto = sheet2 ' or your sheet name here
k = 1
for m = 1 to actsh.usedrange.rows.count
if cells(m,1).value = "Total" then
rows(trim(str(m))).copy
shcpyto.activate
cells(k,1).select
activesheet.paste
k = k+1
end if
next
end sub


"Juan" wrote:

Hi there,
In column A I have some rows with the word Total. I
want
to select those rows and copy to another worksheet
starting in A2.
I been trying to modify some code but without
success.
Can anyone provide some info on this?
Appreciate all the help.

Thanks,

Juan

.



.