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

Report
-
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
-
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!

11 replies

jymie

In your code replace
If Target = "X" Then

with
If LCase(Target) = "x" Then


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

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

CCM 5793 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!
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1576
NIce. thanks for spoon feeding!
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1576
Does the big X mean something else? You could try to switch CASE, or maybe do cell.value.lower, as in LOWER(B1)
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1576
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
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1576
So lower (b1) doesnt help any?
I dont know how to add that to the code I provided. Kind of new to this
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1576
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!