copy rows that contain certain text to a new sheet including the h
 

hello,

i am writing a macro to copy rows which contain certain words.
i am using a macro which i used before to highlight the rows where the word
appears.
but i'm not sure about the code i should use now.
i welcome your comments
mike

copy rows that contain certain text to a new sheet including the h
 

Hi Mike,

Try something like:

'==========
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Dim copyRng As Range
Dim destRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long
Dim arr As Variant

Set WB = ActiveWorkbook '<<=== CHANGE
Set SH = WB.Sheets("Sheet1") '<<=== CHANGE
Set rng = SH.Range("A1:A100") '<<=== CHANGE
Set destRng = WB.Sheets("Sheet2").Range("A2") '<<=== CHANGE

arr = Array("Anne", "Jon", "Kate") '<<===
CHANGE

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In rng.Cells
If Not IsError(Application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<==========


---
Regards,
Norman


"mike" wrote in message
...
hello,

i am writing a macro to copy rows which contain certain words.
i am using a macro which i used before to highlight the rows where the
word
appears.
but i'm not sure about the code i should use now.
i welcome your comments
mike

copy rows that contain certain text to a new sheet including the h
 

Norman,
Thanks for that, its along the lines of what i want.
But,
I want to include an input box, so basically heres what happens:
run the macro
input box - enter bob dave or fred
search column c, cells 4 to 100,
copy the row from column a to l where bob lies to sheet 2

any more suggestions
thanks

mike

"mike" wrote:

hello,

i am writing a macro to copy rows which contain certain words.
i am using a macro which i used before to highlight the rows where the word
appears.
but i'm not sure about the code i should use now.
i welcome your comments
mike

copy rows that contain certain text to a new sheet including the h
 

Hi Mike

Try EasyFilter for this
http://www.rondebruin.nl/easyfilter.htm

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


"mike" wrote in message ...
Norman,
Thanks for that, its along the lines of what i want.
But,
I want to include an input box, so basically heres what happens:
run the macro
input box - enter bob dave or fred
search column c, cells 4 to 100,
copy the row from column a to l where bob lies to sheet 2

any more suggestions
thanks

mike

"mike" wrote:

hello,

i am writing a macro to copy rows which contain certain words.
i am using a macro which i used before to highlight the rows where the word
appears.
but i'm not sure about the code i should use now.
i welcome your comments
mike

copy rows that contain certain text to a new sheet including the h
 

Hi Mike,

Go with Ron's suggestion or try this adaptationn of the suggested code:

'==========
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range
Dim copyRng As Range
Dim destRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long
Dim arr As Variant
Dim res As Variant

Set WB = ActiveWorkbook '<<=== CHANGE
Set SH = WB.Sheets("Sheet1") '<<=== CHANGE
Set Rng = SH.Range("C4:C100")
Set destRng = WB.Sheets("Sheet2").Range("A2") '<<=== CHANGE

res = InputBox("Enter search words separated with a space")

If res = "" Then Exit Sub
arr = Split(res, " ")

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In Rng.Cells
If Not IsError(Application.Match(rCell.Value, arr, 0)) Then
If copyRng Is Nothing Then
Set copyRng = rCell
Else
Set copyRng = Union(rCell, copyRng)
End If
End If
Next rCell

If Not copyRng Is Nothing Then
copyRng.EntireRow.Copy Destination:=destRng
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<==========

--

---
Regards,
Norman



"mike" wrote in message
...
Norman,
Thanks for that, its along the lines of what i want.
But,
I want to include an input box, so basically heres what happens:
run the macro
input box - enter bob dave or fred
search column c, cells 4 to 100,
copy the row from column a to l where bob lies to sheet 2

any more suggestions
thanks

mike

"mike" wrote:

hello,

i am writing a macro to copy rows which contain certain words.
i am using a macro which i used before to highlight the rows where the
word
appears.
but i'm not sure about the code i should use now.
i welcome your comments
mike