Help With This Code

[rockyw]

Please, How do I write this to have the data go to sheet 2, E3. I can't get it to work. Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rMonitor As Range
    Dim rTarget As Range
    Set rMonitor = Range("E3")
    Set rTarget [COLOR=red]= Range("Sheet2", "E3")
[/COLOR]    
    If Not Intersect(Target, rMonitor) Is Nothing Then
        rMonitor.Copy rTarget
    End If
    Set rMonitor = Nothing
    Set rTarget = Nothing
End Sub

 

[GTO]

Presuming "Sheet2" is the name on the tab:

Set rTarget = ThisWorkbook.Worksheets("Sheet2").Range("E3")

 

[rockyw]

Thank you that works but! Excel just keeps returning a 0 from an empty cell. Isnt there a way to stop excel from doing that? For a program so smart it's such a pain sometimes. I want to run a code to copy from sheet 3 cells E3:E20, then paste the data in sheet 2 E3:E20. But if there is an empty cell I do not want excel entering a 0, it messes up a condisional format I am using. Is there a way to keep Excel fro returning a 0 from an empty cell? Thanks

 

[gregtx81]

If you don't want formatting to be copied, uncomment the commented line and comment the rMonitor.Copy line.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rMonitor As Range
    Dim rTarget As Range
    Set rMonitor = ActiveSheet.Range("E3:E20")
    Set rTarget = ThisWorkbook.Worksheets("Sheet2").Range("E3:E20")

    If Not Intersect(Target, rMonitor) Is Nothing Then
        'rTarget.Value = rMonitor.Value
        rMonitor.Copy rTarget
    End If
    Set rMonitor = Nothing
    Set rTarget = Nothing
End Sub

 

[GTO]

Thank you that works but! Excel just keeps returning a 0 from an empty cell. Isnt there a way to stop excel from doing that? For a program so smart it's such a pain sometimes. I want to run a code to copy from sheet 3 cells E3:E20, then paste the data in sheet 2 E3:E20. But if there is an empty cell I do not want excel entering a 0, it messes up a condisional format I am using. Is there a way to keep Excel fro returning a 0 from an empty cell? Thanks

I am probably missing something, but both the below (written in Sheet2's object module) returned blanks for corresponding blanks in Sheet3.

You may want to explain where the conditional formatting is, and what it comprises of.

 

[GTO]

Oopsie, forgot the tests...

<font face=Courier New><SPAN style="color:#007F00">'// in Sheet2 module</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> exa()<br>    Sheet2.Range("B2:B4").Value = Sheet3.Range("B2:B4").Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> exacopy()<br>    Sheet3.Range("B2:B4").Copy Sheet2.Range("B2:B4")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>