ExcelBanter - Excel

Hi,

Not too much infor but here's a macro that will do it. It looks in Column A.
Change the string to search for and the source and destination sheets to suit

Sub stance()
Dim MyRange As Range
Dim CopyRange As Range
Dim MyString As String
Dim SrcSht As String
Dim DestSht As String
Dim LastRow As Long
MyString = "XXX" ' change to suit must be in uppercase
SrcSht = "Sheet1" ' change to suit
DestSht = "Sheet2" ' change to suit
LastRow = Sheets(SrcSht).Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets(SrcSht).Range("A1:A" & LastRow)
For Each c In MyRange

If UCase(c.Value) = MyString Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Copy Destination:=Sheets(DestSht).Range("A1")
End If
End Sub

Mike

"Bhameo" wrote:

How can I search a column for specific values and have all rows that contain
these values copied to another sheet?