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

Select rows and copy to another sheet.
 

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

Select rows and copy to another sheet.
 

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

.

Select rows and copy to another sheet.
 

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

.

Select rows and copy to another sheet.
 

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

.



.

Select rows and copy to another sheet.
 

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

.



.

Select rows and copy to another sheet.
 

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

.



.



.