Hi
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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
vba to copy & paste a range of cells continously with start/stop option
0
Answers
0
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:
Copy/Paste
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()
Start_Timer
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 SubRun 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.
Discussion
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
Thanks
Paul
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
Thanks
Paul
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.
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: 1989) Sep 26, '18 at 9:05 am