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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,714
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
14,825
Office Version
  1. 2010
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
18,714
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
14,825
Office Version
  1. 2010
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,597
Messages
5,838,265
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top