Report

Macro to copy entire row to next workbook [Solved]

Ask a question jymie - Last answered on Dec 1, 2016 at 05:32 PM by ac3mark
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!
Helpful
+1
plus moins
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?  
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 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 Last seen - Dec 1, 2016 at 05:32 PM
NIce. thanks for spoon feeding!
Reply
Leave a comment
Helpful
+0
plus moins
Does the big X mean something else? You could try to switch CASE, or maybe do cell.value.lower, as in LOWER(B1)
ac3mark 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 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 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 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 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 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
Leave a 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!