Worksheet event change with offset for different worksheet

**scooby99** · 12-10-2011, 05:27 PM

Hi!

I have a problem where I would like to enter a value in Sheet1 in range A2:A20.
Each value should be displayed with the Worksheet change event in range C2:C20 in Sheet2

So far I have this which only creates value in C2:C20 in Sheet1, where as I want it in Sheet2. Any idea on how I can do that?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub

On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column = 1 Then
If IsNumeric(Target.Value) Then
Target.Offset(0, 3).Value = Target.Value
Else
Target.Offset(0, 1).Clear
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

**tom1977** · 12-10-2011, 05:37 PM

I do not understand your code but according your description such code should be ok

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Variant
Dim v As Variant
If Not Intersect(Target, Range("a2:a20")) Is Nothing Then

t = Target.Address
v = Target.Value
Sheet2.Range(t) = v

End If

End Sub

**scooby99** · 12-11-2011, 03:09 AM

That worked great, thanks!

Would it be possible to enter a Isnumeric argument, so that when I delete values it doesnt have an effect?

**Winon** · 12-11-2011, 04:13 AM

Hi Tom1977,

Your code will not paste the Range from Sheet1 Column A to Sheet2 Column C. Instead it will paste it to Column A on Sheet2.

**tom1977** · 12-11-2011, 06:08 AM

Winon You right it should be like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Variant
Dim v As Variant
If Not Intersect(Target, Range("a2:a20")) Is Nothing Then

t = Target.Address
v = Target.Value
Sheet2.Range(t).Offset(, 2) = v

End If

End Sub

scooby99

try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Variant
Dim v As Variant
If Not Intersect(Target, Range("a2:a20")) Is Nothing Then
If Not IsEmpty(Target) Then
t = Target.Address
v = Target.Value
Arkusz2.Range(t).Offset(, 2) = v

End If
End If
End Sub

**scooby99** · 12-11-2011, 06:43 AM

Thanks tom1977!

**Winon** · 12-11-2011, 01:51 PM

Hi tom1977,

Thank you for your feedback! Together we shall stand, divided the Code will fail.

Must admit that I have attempted to alter your Code, with my limited VBA knowledge and all, as follws to make it work

Dim t As Variant
Dim v As Variant
Set Target = Sheet2.Range("C2:C20")


t = Target.Address
v = Sheet1.Range("A2:A20")
If Sheet1.Range("a2") <> "" Then
Sheet2.Range(t) = v

End If

Must confess tough that your Code still beats the pants off mine!!!
Well done.