Macro to copy entire row to next workbook

 Blocked Profile -

I have the following macro to copy entire row when the column has a capital X. I would like to also copy when a small x is present.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Z:Z")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim foundVal As Range
If Target = "X" Then
Target.EntireRow.Copy Sheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
ElseIf Target = "" Then
Set foundVal = Sheets("Sheet4").Range("A:A").Find(Target.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole)
If Not foundVal Is Nothing Then
MsgBox ("Sequence number " & Target.Offset(0, -1) & " not found.")
End If
End If
Application.ScreenUpdating = True
End Sub

what needs to be added to make this happen?

thank you!

2 replies


In your code replace
If Target = "X" Then

If LCase(Target) = "x" Then

or simply
If Target = "X" Or Target = "x" Then
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Thanks that works perfect! Thank you so much I have learned something new today, thanks again!
Blocked Profile
NIce. thanks for spoon feeding!

Does the big X mean something else? You could try to switch CASE, or maybe do cell.value.lower, as in LOWER(B1)
Blocked Profile
Please share your solution!
I dont have a solution. I just want the macro to pick up more than one characture in a row and copy to next worksheet. Capitol X or lower case x
Blocked Profile
So lower (b1) doesnt help any?
I dont know how to add that to the code I provided. Kind of new to this
Blocked Profile
Your actual formula would be =lower(b1). That command will take what ever is in B1 and make it lower case. Then you can compare your value to the lower case value of B1!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!