Macro to copy entire row to next workbook [Solved/Closed]

jymie - Nov 22, 2016 at 08:12 PM - Latest reply: ac3mark 9164 Posts Monday June 3, 2013Registration dateModeratorStatus April 19, 2018 Last seen
- Dec 1, 2016 at 05:32 PM
Hello,





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
foundVal.EntireRow.Delete
Else
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!
See more 

10 replies

+1
Helpful
2
jymie

In your code replace
If Target = "X" Then

with
If LCase(Target) = "x" Then


or simply
If Target = "X" Or Target = "x" Then
Was this answer helpful?  
Thanks that works perfect! Thank you so much I have learned something new today, thanks again!
ac3mark 9164 Posts Monday June 3, 2013Registration dateModeratorStatus April 19, 2018 Last seen - Dec 1, 2016 at 05:32 PM
NIce. thanks for spoon feeding!
ac3mark 9164 Posts Monday June 3, 2013Registration dateModeratorStatus April 19, 2018 Last seen - Nov 22, 2016 at 08:39 PM
0
Helpful
6
Does the big X mean something else? You could try to switch CASE, or maybe do cell.value.lower, as in LOWER(B1)
ac3mark 9164 Posts Monday June 3, 2013Registration dateModeratorStatus April 19, 2018 Last seen - Nov 28, 2016 at 04:34 PM
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
ac3mark 9164 Posts Monday June 3, 2013Registration dateModeratorStatus April 19, 2018 Last seen - Nov 28, 2016 at 05:32 PM
So lower (b1) doesnt help any?
I dont know how to add that to the code I provided. Kind of new to this
ac3mark 9164 Posts Monday June 3, 2013Registration dateModeratorStatus April 19, 2018 Last seen - Nov 30, 2016 at 04:54 PM
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!