VB Script help needed

[steve2115]

Need help with vb script to do the following


Copy & Paste special (values) from Sheet “A3”.Range(B6:E8)
AND
Copy & Paste special (values) from Sheet “Timeline”.Cell(F6)
TO
Sheet “Change Log”
WHEN
Sheet “A3”.Cell(M3=”YES”)

 

[bbott]

Put this in the Change Log worksheet object code, not in a general module. You didn't specify where the values are going, so I just put them in the corresponding ranges on Change Log.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Set Target = Range("M3")
    
    If Target = "Yes" Then
        Sheets("A3").Range("B6:E8").Copy Sheets("Change Log").Range("B6:E8")
        Sheets("Timeline").Range("F6").Copy Sheets("Change Log").Range("F6")
    End If
    
End Sub

 

[steve2115]

Thanks for the quick response...Will the script run automatically? Also, is there a way to stop the script after it pastes the values onto Change Log? I pasted the script in the Change Log object screen but did not get any results.

 

[bbott]

I apologize. That should be in the A3 object sheet, not the Change Log sheet. Yes, it will run automatically.

Depending how and when you want the code to stop calculating, you can make it stop. I would suggest something like:

Private Sub Worksheet_Change(ByVal Target As Range)     

          [B][COLOR=#ff0000]If Sheets("Change Log").Range("F6") <> "" Then Exit Sub[/COLOR][/B]

          Set Target = Range("M3")

      If Target = "Yes" Then         
          Sheets("A3").Range("B6:E8").Copy Sheets("Change Log").Range("B6:E8")
          Sheets("Timeline").Range("F6").Copy Sheets("Change Log").Range("F6")
     End If

End Sub

 

[steve2115]

I pasted code in A3 object sheet but I am still not getting any values pasted to Change Log. ???? I have confirmed that (M3=Yes).

 

[bbott]

Do you have macros enabled?

Also, did the cell already = Yes? You would need to type Yes after adding the code (otherwise, there is no change to trigger the event).

 

[steve2115]

Just got it... M3 is case sensitive.... when I typed "yes", no luck, typed "Yes", good to go. However, I am getting the formula results for "F6" and not the value. Need to have the value (paste special?)..Thanks

 

[bbott]

This modification should work.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Sheets("Change Log").Range("F6") <> "" Then Exit Sub
         
    Set Target = Range("M3")

    If Target = "Yes" Then
        Sheets("Change Log").Range("B6:E8") = Sheets("A3").Range("B6:E8").Value
        Sheets("Change Log").Range("F6") = Sheets("Timeline").Range("F6").Value
    End If

End Sub