I am looking to add data from an opc server into cell A3, everytime data
comes in I want the old data to drop down one.A4, A5, A6 and so on.

Hi Mike,

this worked for me when ever I entered a value into A3...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" Then
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Range("A:A").Rows.Count, 1) _
..End(xlUp).Row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Range("A4").Value = Range("A3").Value
Case Else
Set rgOldValues = Me.Range("A4:A" & iLastRow)
rgOldValues.Cut _
Destination:=Me.Range("A5:A" & iLastRow + 1)
Range("A4").Value = Range("A3").Value
End Select
Application.EnableEvents = True
End If
End Sub


Could be a horse of a different colour though when the change is from
an opc server.

To get the code in place...

1. Copy it
2. Right click the worksheet's sheet tab then select "View Code" from
the popup menu.
3. Paste the code into the worksheet's code module that appears.
4. Press Alt + F11 to get back to the worksheet.

Ken Johnson

Everything worked great. I truly appreciate that. Is there something i
could use that would let me do multiple columns. B3, C3, D3, etc?:)


--
mikespeck
------------------------------------------------------------------------
mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946
View this thread: http://www.excelforum.com/showthread...hreadid=546673

Hi Mike,

try this, which extends the effect from A3 up to G3. If that's not
enough columns then just edit the address "A3:G3" in the first line "If
Not Intersect(Range(Target.Address), Me.Range("A3:G3")) Is Nothing
Then" to suit your needs, eg changing that address to "A3:J3" will give
you 10 columns..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Me.Range("A3:G3")) _
Is Nothing Then
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, 1) _
..End(xlUp).Row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
Case Else
Set rgOldValues = Me.Range(Cells(Target.Row + 1, Target.Column), _
Cells(iLastRow, Target.Column))
rgOldValues.Cut _
Destination:=Me.Range(Cells(Target.Row + 2, Target.Column), _
Cells(iLastRow + 1, Target.Column))
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
End Select
Application.EnableEvents = True
End If
End Sub
Ken Johnson.

Hi Mike,

two things..

1. There's a mistake in my last reply, which I have now fixed up.

2. To cover the possibility of the data reaching the bottom of the
sheet I have included a line that clears the bottom-most row of the
target column so that there is room for moving all the old data down
one row....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Me.Range("A3:G3")) _
Is Nothing Then
Me.Cells(Me.Range("A:A").Rows.Count, Target.Column).Clear
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, Target.Column) _
..End(xlUp).Row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
Case Else
Set rgOldValues = Me.Range(Cells(Target.Row + 1, Target.Column), _
Cells(iLastRow, Target.Column))
rgOldValues.Cut _
Destination:=Me.Range(Cells(Target.Row + 2, Target.Column), _
Cells(iLastRow + 1, Target.Column))
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
End Select
Application.EnableEvents = True
End If
End Sub


Ken Johnson

Thanks Ken I appreciate all of your help! Everything works great.
Thanks again,
Mike


--
mikespeck
------------------------------------------------------------------------
mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946
View this thread: http://www.excelforum.com/showthread...hreadid=546673