Hi, it's been a long time and my credentials still work. (and I still remembered them).
I have a spreadsheet that works (a hack job because I don't understand code very well)
I found this code online, some of it I get, other parts I don't.
In my test code I search for a value in column 'C' and if that value = john, the entire row is moved to sheet2 (the job is done)
My goal is to be able to search for more than one value. I would to search for john and dan in column see and if either of those conditions are met I would like the entirerow to move to sheet2. I've spent a lot of time repeating functions and looking for solutions - thought it was time to ask the experts.
Here's what I have - and please excuse me if I have extraneous in this code, but it does work with one name: john. I would also like it to NOT be case sensitive if possible...
This sub is called in sheet1 where you right click the bottom tab and add code (General)
Thanks ahead for any help in getting 2 or more names I can search for.
I have a spreadsheet that works (a hack job because I don't understand code very well)
I found this code online, some of it I get, other parts I don't.
In my test code I search for a value in column 'C' and if that value = john, the entire row is moved to sheet2 (the job is done)
My goal is to be able to search for more than one value. I would to search for john and dan in column see and if either of those conditions are met I would like the entirerow to move to sheet2. I've spent a lot of time repeating functions and looking for solutions - thought it was time to ask the experts.
Here's what I have - and please excuse me if I have extraneous in this code, but it does work with one name: john. I would also like it to NOT be case sensitive if possible...
Code:
Sub Cheezy()
'Updated by Kutools for Excel 2017/8/28
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("C1:C" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "john" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "john" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
This sub is called in sheet1 where you right click the bottom tab and add code (General)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C100")) Is Nothing Then
If Target.Value = "Done" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
'Target.ClearContents
Call Cheezy
Application.EnableEvents = True
Application.ScreenUpdating = True
Else
End If
End If
End Sub
Thanks ahead for any help in getting 2 or more names I can search for.