Vba copy & paste to another sheet

[jaxs2009]

I have this string of VBA code in Sheet3(WIP), the first "Option Explicit" runs as written.

The next VBA code runs as written

However the bottom VBA code "Option Explicit" very similar but for reason it doesn't run as written, I get no error message. maybe there is a conflict with two VBA codes essentially the same with different targets.

Hope you can help


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Columns("Q")) Is Nothing Then Exit Sub
If Target.Value = vbNullString Then Exit Sub


Dim ws As Worksheet: Set ws = Sheets("CHANGE ORDERS")


If Target.Value = "YES" Then
Range(Cells(Target.Row, "C"), Cells(Target.Row, "O")).Copy ws.Range("C" & Rows.Count).End(3)(2)
End If


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Application.Calculate
End If

End Sub

Private Sub Worksheet(ByVal Target As Range)


If Intersect(Target, Columns("R")) Is Nothing Then Exit Sub
If Target.Value = vbNullString Then Exit Sub


Dim ws As Worksheet: Set ws = Sheets("REWORK")


If Target.Value = "YES" Then
Range(Cells(Target.Row, "C"), Cells(Target.Row, "M")).Copy ws.Range("C" & Rows.Count).End(3)(2)
End If


End Sub

 

[My Aswer Is This]

Would you please explain in words what your total overall objective is with these three or four scripts.

Like this one for example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Application.Calculate
End If
End Sub

All this does is every time you select a different cell the script looks to see if you have copied something the script needs to calculate.

 

[jaxs2009]

Yes, hopefully I can do that for you,

I have excel 2007

The VBA code you pasted in your response in conjunction with conditional formatting will highlight active row (when selected) between column "C" and "what ever" that is determined in the conditional formatting formula for each of the sheets in use as it varies in the workbook.

The other two VBA are basically the same but different target sheets and ranges will copy and paste data as outlined in the VBA code, like I mentioned the first "Option Explicit" I listed works as written the other does not.

Thank you.

 

[DanteAmor]

Update your code with the following. The change events must be in one.

Option Explicit


Private Sub [COLOR=#0000ff]Worksheet_Change[/COLOR](ByVal Target As Range)
    Dim ws As Worksheet
    
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    If [COLOR=#0000ff]Not[/COLOR] Intersect(Target, [COLOR=#0000ff]Columns("Q")[/COLOR]) Is Nothing Then
        If Target.Value = "YES" Then
            Set ws = Sheets("CHANGE ORDERS")
            Range(Cells(Target.Row, "C"), Cells(Target.Row, "O")).Copy ws.Range("C" & Rows.Count).End(3)(2)
        End If
    End If
    
    If [COLOR=#0000ff]Not[/COLOR] Intersect(Target, [COLOR=#0000ff]Columns("R")[/COLOR]) Is Nothing Then
        If Target.Value = "YES" Then
            Set ws = Sheets("REWORK")
            Range(Cells(Target.Row, "C"), Cells(Target.Row, "M")).Copy ws.Range("C" & Rows.Count).End(3)(2)
        End If
    End If
    
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = False Then
        Application.Calculate
    End If
End Sub

 

[My Aswer Is This]

Well me just asking a question has now got you a answer. So I will move on.

 

[jaxs2009]

Good morning, well here in West Coast of the USA. The VBA code you provided works great, thank you. I reviewed this code to see where I went wrong and now understand it.

Have a great day. jaxs2009

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.