Another option that I like to use with this kind of stuff is not move the data
at all. I apply Data|filter|autofilter to my range. Then I can filter the
column and see the status of each category.
I find it much more useful to keep my data in one spot. You can do lots more
things with it.
If you don't like that idea, try recording a macro when you apply the
autofilter, filter on each value, and copy the visible rows to the sheets you
want.
(But I wouldn't move them. I find filtering sufficient and easier for any next
step that comes up.)
Bruccce wrote:
I do want to REALLY MOVE but I want to move ALL that have the tested for
string.
Example.
I have a sheet with the following columns
Name, phone, results (this is over simplified!)
say results are "Not Interested" for several records.
I would like to move all of the records to sheet "Not_Interested"
same for calls like "Disconnected" to disconnected
etc...
And then that would leave me with a contacts that I still need to call.
Thanks
Bruce
"Dave Peterson" wrote in message
...
Really move (not copy?).
And is it just the first row where you find something.
I looked in column A of sheet1. I pasted into of sheet2 (in the next open
cell
in column A).
Option Explicit
Sub MoveRowsContaining()
Dim FoundCell As Range
Dim myRng As Range
Dim WhatToFind As String
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim destCell As Range
Set fromWks = Worksheets("sheet1")
Set toWks = Worksheets("sheet2")
WhatToFind = "Hi there"
With fromWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
Set FoundCell = Nothing
With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
LookIn:=xlValues, _
lookat:=xlPart, _
MatchCase:=False, _
after:=.Cells(.Cells.Count), _
searchorder:=xlNext)
If FoundCell Is Nothing Then
MsgBox "not found"
Else
With toWks
Set destCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
FoundCell.EntireRow.Copy _
Destination:=destCell
FoundCell.EntireRow.Delete 'really move???
End If
End With
End With
End Sub
Modify that .find command. I looked at xlpart (not xlwhole).
Bruccce wrote:
I would like to move a row to a different worksheet. I have come across
the
following code to delete a cell. Can it be modified or is there a better
way....
Thanks
Bruce
_________________
Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow As Long
ans = InputBox("What string do you want rows to be deleted if they
contain
it?")
Application.ScreenUpdating = False
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = lrow To 1 Step -1
With Cells(r, 1)
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next r
Application.ScreenUpdating = True
End Sub
--
Dave Peterson
--
Dave Peterson