Report

Macro to copy entire row to next workbook [Solved]

Ask a question jymie - Last answered on 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 
Helpful
+1
moins plus
jymie

In your code replace
If Target = "X" Then

with
If LCase(Target) = "x" Then


or simply
If Target = "X" Or Target = "x" Then
Jymie- Nov 30, 2016 at 08:54 PM
Thanks that works perfect! Thank you so much I have learned something new today, thanks again!
Reply
ac3mark 6224Posts Monday June 3, 2013Registration date ModeratorStatus December 8, 2016 Last seen - Dec 1, 2016 at 05:32 PM
NIce. thanks for spoon feeding!
Reply
Add comment
Helpful
+0
moins plus
Does the big X mean something else? You could try to switch CASE, or maybe do cell.value.lower, as in LOWER(B1)
ac3mark 6224Posts Monday June 3, 2013Registration date ModeratorStatus December 8, 2016 Last seen - Nov 28, 2016 at 04:34 PM
Please share your solution!
Reply
Jymie- Nov 28, 2016 at 05:18 PM
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
Reply
ac3mark 6224Posts Monday June 3, 2013Registration date ModeratorStatus December 8, 2016 Last seen - Nov 28, 2016 at 05:32 PM
So lower (b1) doesnt help any?
Reply
Jymie- Nov 28, 2016 at 05:37 PM
I dont know how to add that to the code I provided. Kind of new to this
Reply
ac3mark 6224Posts Monday June 3, 2013Registration date ModeratorStatus December 8, 2016 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!
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!