copy to another sheet based on column value
 

Hi, Im trying to find a way (im guessing a macro) that I can copy all the
rows on a page that have "RES" as the value in the b column to another sheet,
pasting them starting in the first row that is open on the new sheet. Can
anyone help with this? It would be greatly appreciated.

copy to another sheet based on column value
 

Do you want a new sheet or do you want to append to the first blank row of an
existing sheet?

"Mike" wrote:

Hi, Im trying to find a way (im guessing a macro) that I can copy all the
rows on a page that have "RES" as the value in the b column to another sheet,
pasting them starting in the first row that is open on the new sheet. Can
anyone help with this? It would be greatly appreciated.

copy to another sheet based on column value
 

Hi mike

Try this
http://www.rondebruin.nl/copy5.htm

Or this Add-in
http://www.rondebruin.nl/easyfilter.htm


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



"Mike" wrote in message ...
Hi, Im trying to find a way (im guessing a macro) that I can copy all the
rows on a page that have "RES" as the value in the b column to another sheet,
pasting them starting in the first row that is open on the new sheet. Can
anyone help with this? It would be greatly appreciated.

copy to another sheet based on column value
 

Here is some code that creates a new sheet for you

Public Sub CopyRows()
Dim wksToSearch As Worksheet
Dim wksToPaste As Worksheet
Dim strToFind As String
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngPaste As Range
Dim strFirstAddress As String

strToFind = "RES"
Set wksToSearch = ActiveSheet
Set rngToSearch = wksToSearch.Range("B1").EntireColumn
Set wksToPaste = Worksheets.Add
Set rngPaste = wksToPaste.Range("A1")
Set rngFound = rngToSearch.Find(strToFind, , , xlPart)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
rngFound.EntireRow.Copy rngPaste
Set rngFound = rngToSearch.FindNext(rngFound)
Set rngPaste = rngPaste.Offset(1, 0)

Loop Until rngFound.Address = strFirstAddress

End If
End Sub

If this is a one of thing then doing a filter is going to be the easiest
though...

HTH

"Jim Thomlinson" wrote:

Do you want a new sheet or do you want to append to the first blank row of an
existing sheet?

"Mike" wrote:

Hi, Im trying to find a way (im guessing a macro) that I can copy all the
rows on a page that have "RES" as the value in the b column to another sheet,
pasting them starting in the first row that is open on the new sheet. Can
anyone help with this? It would be greatly appreciated.

copy to another sheet based on column value
 

Just in case you would have been happy with any solution, you also had the
option of simply sorting the data on that column then copying the block that
meets your criteria.

You could also just filter on Col, B, use Edit / Go To / Special Cells /
Visible and then copy them to your new sheet.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Mike" wrote in message
...
Hi, Im trying to find a way (im guessing a macro) that I can copy all the
rows on a page that have "RES" as the value in the b column to another
sheet,
pasting them starting in the first row that is open on the new sheet. Can
anyone help with this? It would be greatly appreciated.