vba to copy & paste a range of cells continously with start/stop option



I want to be able to start a process to copy cells K9:K67 and paste them to AM9:AM67 continously at the rate of 2 or 4 times a second (if that's possible) Ideally with a start and stop button

Thanks, Paul



Selected Answer

It probably won't funcation exactly the way you want since you are doing it through Excel.

That said, here are some macros to get you started:


Range("K9:K67").Copy Range("AM9:AM67 ")

More info here (TeachExcel tutorial): Excel Copy/Paste with VBA

Copy/Paste Inside "Timer"

This is some very generic time code with your copy/paste code inside it.

Dim TimerActive As Boolean
Sub StartTimer()
End Sub

Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:01"), "Timer"
End Sub

Private Sub Stop_Timer()
    TimerActive = False
End Sub

Private Sub Timer()
    If TimerActive Then

        Range("K9:K67").Copy Range("AM9:AM67 ")

        Application.OnTime Now() + TimeValue("00:00:01"), "Timer"

    End If
End Sub

Run Start_Timer to start it and Stop_Timer to stop it.

It runs once per second.

I think that once you run this you will not like the result and will choose another route to solve your issue; in that case, please explain your issue better. The fact of the matter is that Excel does not perform this kind of operation well.



Thank you for your answer Don, and you are correct it's probably not the best solution .. but I am a basic user and you may have a better way, if I can explain ..

The spreadsheet is connected to a trading platform and  numbers populate the sheet when I connect it. There is nothing special about their spreadsheet in fact I can just connect a newly created blank and the numbers populate the approrriate columns.
What i want to achieve is the following ...
as an example .. in this instance is to monitor a col ie "H" and when the numbers change to have the "new updated value" minus the "old value" put into col AN ..
so if H9 is 5.8, then it changes to 6.0, then AN9 should show 0.2, if it then changes to 5.5 then AN9 should show -0.5 and so on .. and at times this needs to happen fast .. and continously ..
I have put an example vba below of how I first tried to avhieve this...
The problem is that vba dosn't see the numbers in Col H, it thinks the cells are empty. I am told the numbers are transfered via a method called Dymanic data exchange
And so I came up with the idea to copy the numbers from col H and paste them in another col and just have a sum in the final col which seems to work on the face of it,  but as you say not ideal when the numbers are changing possibly at several times per second at times.
Grateful for any help
Sherman51 (rep: 6) Sep 26, '18 at 6:52 am
Option Explicit

Dim OldVal
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H9:H16")) Is Nothing Then
    Application.EnableEvents = False
    OldVal = Target.Value
    Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H9")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN9") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H10")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN10") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H11")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN11") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    End Sub

Sherman51 (rep: 6) Sep 26, '18 at 6:54 am
Please update your question with all of this information, it is hard to read in a "discussion comment". That said you need to use some sort of data linking for this that is probably specific to your trading platform. If real money is involved, please don't use Excel to refresh on a regular basis and TIMELY basis upon which stock market decisions will be made. It is just sooo easy for Excel to crash, stop updating, have other issues, since it was not made to do this. 

Linking with a system that spits the data to Excel and refreshes it, though, should work fine, though, not as smoothly as going through the app that 'spits' the information in the first place.
don (rep: 1745) Sep 26, '18 at 9:05 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login