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

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

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

.

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

.

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

.



.

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

.



.

Hello Ron,
thanks, but since the cells contain subtotal formula, when
it does this it will show #div. But i was able to get what
I want by doing a couple of steps. First copied to another
sheet using paste values then did the autofilter then I
copied again and paste values. This works but was hoping
didn't had to do a few steps.

So thanks for the help really appreciated.

Thanks,

Juan
-----Original Message-----
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

.



.



.