copy to another sheet based on column value

[Guest]

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.

 

[Guest]

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

 

[Ron de Bruin]

Hi mike

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

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

 

[Guest]

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

 

[Ken Wright]

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.