Vba copy & paste to another sheet

jaxs2009

Board Regular
Joined
Nov 28, 2010
Messages
200
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,882
Office Version
  1. 2013
Platform
  1. Windows
Would you please explain in words what your total overall objective is with these three or four scripts.

Like this one for example:

Code:
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.
 
Last edited:

jaxs2009

Board Regular
Joined
Nov 28, 2010
Messages
200
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Update your code with the following. The change events must be in one.

Code:
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-known Member
Joined
Jul 5, 2014
Messages
16,882
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

jaxs2009

Board Regular
Joined
Nov 28, 2010
Messages
200
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,467
Members
409,883
Latest member
asharris90
Top