Select rows and copy to another sheet.

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>> 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" <[email protected]> wrote in message
news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>> 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
>>>>>>
>>>>>.
>>>>>
>>>
>>>
>>>.
>>>
>
>
>.
>