Syntax help with range

I am having trouble with variables/syntax in trying to copy and shift
data from one worksheet to another (Excel 2000)
My Input sheet data is a single column
My Output sheet (in this case) is 2 columns.

Hopefully you can see what I am trying to do with this 'crude' code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewTarget As Range
If Target.Column <> 1 Then Exit Sub
With Sheets("Output")
NewRow = (Target.Row + (Target.Row - 1) * 3)
NewTarget.Row = NewRow
If Target.Row >= 18 Then
NetTarget.Column = 2
Else
NetTarget.Column = 1
End If
.Range(NewTarget.Address).Value = Target.Value
End With
End Sub

Try this:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRow As Long, NewColumn As Integer
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column <> 1 Then Exit Sub
With Sheets("Output")
NewRow = (Target.Row + (Target.Row - 1) * 3)
If Target.Row >= 18 Then
NewColumn = 2
Else
NewColumn = 1
End If
.Cells(NewRow, NewColumn).Value = Target.Value
End With
ws_exit:
Application.EnableEvents = True
End Sub


HTH

"ZZBC" wrote:

> I am having trouble with variables/syntax in trying to copy and shift
> data from one worksheet to another (Excel 2000)
> My Input sheet data is a single column
> My Output sheet (in this case) is 2 columns.
>
> Hopefully you can see what I am trying to do with this 'crude' code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewTarget As Range
> If Target.Column <> 1 Then Exit Sub
> With Sheets("Output")
> NewRow = (Target.Row + (Target.Row - 1) * 3)
> NewTarget.Row = NewRow
> If Target.Row >= 18 Then
> NetTarget.Column = 2
> Else
> NetTarget.Column = 1
> End If
> .Range(NewTarget.Address).Value = Target.Value
> End With
> End Sub
>