Adding rows to create list from DDE data

[Guest]

I have data updated via a DDE link and each time the DDE link updates I need to capture the data so that it creates a list by adding a new row. I have used the SheetCalculateEvent to trigger the update to cell B2 (which links to another cell which is the DDE link) which then invokes the SheetChangeEvent.

My problem is that on each update a new row is inserted at the top of the list for the new data and this causes the screen to flicker as all the data is moved. I would rather have the data just build progressively so that the new data is added to the bottom of the list. Can someone help how best to achieve this? My code is

Private Sub Worksheet_Calculate(

Worksheet_Change Range("$B$2"

End Su

Private Sub Worksheet_Change(ByVal Target As Range
If Target.address = "$B$2" The
Sheets("intraday").Selec
Range("A2:E2").Selec
Selection.Cop
Range("A4").Selec
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
:=False, Transpose:=Fals
If Range("B4") > Range("B5") The
Range("I10").Selec
Selection.EntireRow.Inser
Range("A4:H4").Selec
Selection.Cop
Range("I10").Selec
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
:=False, Transpose:=Fals
Range("A4:E4").Selec
Selection.Cop
Range("A5").Selec
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
:=False, Transpose:=Fals
End I
End I

End Su

I should explain that I copy the range A2..E2 (which contains the target cell) to A4..E4, and this is then compared to the same data prior to the last update which is contained at A5..E5 to do some extra calcs in F5..H5. If the new update at B4 is > than the last update at B5, then the new update is added to the list at I10..P10

Any help gratefully received

Thanks, Simon

 

[Kevin Beckham]

Simon,
You could bracket your code with
Application.ScreenUpdating = False
:
:
Application.ScreenUpdating = True
and remove all the Select statements (much neater), viz.

If Target.address = "$B$2" Then
Application.ScreenUpdating = False
With Sheets("intraday")
.Range("A2:E2").Copy
.Range("A4").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
If .Range("B4") > .Range("B5") Then
.Rows(10).Insert
.Range("A4:H4").Copy
.Range("I10").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("A4:E4").Copy
.Range("A5").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End If
End With
Application.ScreenUpdating = False
End If

-----Original Message-----
I have data updated via a DDE link and each time the DDE

link updates I need to capture the data so that it creates
a list by adding a new row. I have used the
SheetCalculateEvent to trigger the update to cell B2
(which links to another cell which is the DDE link) which
then invokes the SheetChangeEvent.

My problem is that on each update a new row is inserted

at the top of the list for the new data and this causes
the screen to flicker as all the data is moved. I would
rather have the data just build progressively so that the
new data is added to the bottom of the list. Can someone
help how best to achieve this? My code is:

Private Sub Worksheet_Calculate()

Worksheet_Change Range("$B$2")

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$B$2" Then
Sheets("intraday").Select
Range("A2:E2").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues,

Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
If Range("B4") > Range("B5") Then
Range("I10").Select
Selection.EntireRow.Insert
Range("A4:H4").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues,

Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
Range("A4:E4").Select
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues,

Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
End If
End If

End Sub

I should explain that I copy the range A2..E2 (which

contains the target cell) to A4..E4, and this is then
compared to the same data prior to the last update which
is contained at A5..E5 to do some extra calcs in F5..H5.
If the new update at B4 is > than the last update at B5,
then the new update is added to the list at I10..P10

 

[Simon]

Kevin

Thanks a million. Have tried your suggestion and it works a treat.

Many thanks, Simon