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