Simple Copy and Update Cell Value Every Minute

To Whom It May Concern:

I have the following issue.

I have an Excel file with several sheets as tabs inside the file.
The first tab of the Excel file is connected to stream live data feed.

What I want is to take the values from a particular cell on that "live feed sheet" at 1-minute increments and populate these values one after another into cells in another tab of the same file.
The value are updated continuously during the day.

So, let's say the values in a live feed are located in A1 cell of Worksheet 1.
I want values from this cell in 1-min increments to populate into cells A1, A3, A5, A7, A9... etc on Worksheet 2 of the same excel file.

Also, in cells A4, A6, A8, A10, A12, etc... I need a formula where A4 = (A3-A1); A6 = (A5-A3); A8 = (A7-A5), and etc. Is it possible to create only one formula that calculates difference between preceding cell - (preceding cell - 2)?

Could you please recommend what to do? I'm not an Excel expert and not a macro person.

I have Microsoft Excel 2010, Student Edition.

Put this macro in a standard code-module:

Code:

---

Sub blah()
With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)    'destination sheet, bottom of column A
  .Value = Sheets("Sheet1").Range("A1").Value    'source sheet and cell
  .Offset(1).FormulaR1C1 = "=R[-1]C-R[-3]C"    'add a formula in the cell below
End With
If UCase(Sheets("Sheet2").Range("B1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
End Sub

---

Run it once to start the process.
To stop the process enter the word stop in the cell specified in the last line of the macro. It will produce one more pair of values then stop.
To restart the process, remove the word stop from the cell and run blah.
To keep it simple, it starts at row 2, not row 1; you can live with deleting that cell at the top of column A later, any time after the first pair of values has arrived. The macro always adds information below existing data in column A.

P45Cal, thank you very much for replying. Let me try this!
I'm not VBA savvy, please give me a couple of hours :-)
Thank you very much for your reply!!!

Ok, I just realized that I gave the wrong cell count.
Ok, here we go, here is what is needed.

Live data is being populated on Sheet 1, cells: P11-P24 and X11-X24.
Here is what I need (I attached a screenshot): in 1-min increments, I need data populated into Sheet 9 for the following cell schema (attached), it is better to see once, than to explain in multiple sentences.

Another question I wanted to ask you: is it possible to also create a rule, where alert is issued whenever the value in columns "Difference" exceeds 2,000 (>=2,000) (an audio and a color alert).

I would be happy to send you support via PayPal for this solution.

Thank you!!!Attachment 22812

Quote:

---

Originally Posted by Pastry_chef

Ok, I just realized that I gave the wrong cell count.

---

Yeh, sure.
Talk about moving the goal posts. You've changed their width, made 'em taller than they're wide, oh, and changed the name of the game to Tennis.

Code:

---

Sub blah()
With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14)    'destination sheet, to the right of data in row 3
  .Value = Sheets("Sheet1").Range("P11:P24").Value    'source sheet and range 1
  .Offset(, 1).Value = Sheets("Sheet1").Range("X11:X24").Value    'source sheet and range 2
  If .Column = 5 Then .Offset(, -1).ClearContents    'removes formulae from column D
  .Offset(, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
End With
If UCase(Sheets("Sheet9").Range("A1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
End Sub

---

Quote:

---

Originally Posted by Pastry_chef

is it possible to also create a rule, where alert is issued whenever the value in columns "Difference" exceeds 2,000 (>=2,000) (an audio and a color alert).

---

There are 14 values in those columns. Should they all exceed 2,000, one of them, some of them? to set off an alert.

Please don't be upset with me :-)
I'm a very nice person and I'm very grateful for your help!!! This is sincere and truly!!!
One more question.
If I wanted to add more rows below, like attached. How do I expand your code, so that adjust it myself in the future without bothering you again? B/c I know you're busy.
Please send me your PayPal link, I would be very happy to compensate you for your help. I'm not interested in milking your help for free!!!
I'm truly very appreciative :-)Attachment 22813

If any one cell (under column "Difference") exceeds >=2,000, then it sends alert.
All of them, will Never get to that value simultaneously and a group will not get to that value there.
It will be a cell, here and there.

Your screenshot isn't quite high enough resolution to read properly. Can you provide a better one?
I have little time today but… I'll answer both your questions when I can, probably tomorrow UK time.

I have copied everything into a separate file, called it "Sheet 9".
So, the data (in reality) would go from Sheet 1 to Sheet 9 of the SAME file.

This "Sheet 9" that is attached is just a "dummy" excel file to show layout.

In answer to msg#7:

Code:

---

Sub blah()
With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14)    'destination sheet, to the right of data in row 3
  .Value = Sheets("Sheet1").Range("P11:P24").Value    'source sheet and range 1
  .Offset(15).Value = Sheets("Sheet1").Range("AF11:AF24").Value    'source sheet and range 3
  .Offset(, 1).Value = Sheets("Sheet1").Range("X11:X24").Value    'source sheet and range 2
  .Offset(15, 1).Value = Sheets("Sheet1").Range("AD11:AD24").Value    'source sheet and range 4
  If .Column = 5 Then
    .Offset(, -1).ClearContents    'removes formulae from column D
    .Offset(15, -1).ClearContents    'removes formulae from column D
  End If
  .Offset(, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
  .Offset(15, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
End With
If UCase(Sheets("Sheet9").Range("A1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
End Sub

---

msg#8 response coming presently…

For sound:

Code:

---

'Public Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long                  'needed for Beep with arguments.
Sub blah()
With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14)    'destination sheet, to the right of data in row 3
  .Value = Sheets("Sheet1").Range("P11:P24").Value    'source sheet and range 1
  .Offset(15).Value = Sheets("Sheet1").Range("AF11:AF24").Value    'source sheet and range 3
  .Offset(, 1).Value = Sheets("Sheet1").Range("X11:X24").Value    'source sheet and range 2
  .Offset(15, 1).Value = Sheets("Sheet1").Range("AD11:AD24").Value    'source sheet and range 4
  If .Column = 5 Then
    .Offset(, -1).ClearContents    'removes formulae from column D
    .Offset(15, -1).ClearContents    'removes formulae from column D
  End If
  .Offset(, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
  .Offset(15, 2).FormulaR1C1 = "=RC[-2]-RC[-5]"    'add a formulae in the next column
  If .Column <> 2 Then
    If Application.Max(.Offset(, 2), .Offset(15, 2)) > 2000 Then
      For Each cll In Union(.Offset(, 2), .Offset(15, 2)).Cells
        If cll.Value > 2000 Then cll.Interior.Color = 255
      Next cll
      Application.ScreenUpdating = True
      'choose one or more of the following:
      Beep 'Beep (windows alert sound) without arguments requires the DISABLING/ABSENCE/REMOVAL of the Public Declare function at the top of the code module
'      Beep 1200, 500    'Beep with arguments (frequency,duration) NEEDS the Public Declare function at the top of the code module'
'      Beep 1200, 500    'Beep with arguments (frequency,duration) NEEDS the Public Declare function at the top of the code module'
      Application.Speech.Speak "at least one cell has a difference over 2000"



    End If
  End If
End With
If UCase(Sheets("Sheet9").Range("A1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "blah"
End Sub

---

Note the comments in the code. The top Public Declare line is currently disabled by prefixing it with an apostrophe which reduces it to a comment.
There are many more sound possibilities, see:
https://www.ozgrid.com/forum/forum/h...ain-percentage
https://www.ozgrid.com/forum/forum/h...t-pausing-code
http://www.cpearson.com/excel/PlaySound.aspx

To stop the macro, do I enter stop in between <>?

I get an error, so far.
Runtime error 9. Subscript out of range.
I'm asked to debug the follow code line:
With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14) 'destination sheet, to the right of data in row 3

Quote:

---

Originally Posted by Pastry_chef

To stop the macro, do I enter stop in between <>?

---

No. Just stop, or Stop, or sToP, no spaces in cell A1 of Sheet9 (The sheet called Sheet9 on its tab, not the workbook called Sheet9.
In Excel there are Workbooks (files), which contain (at least one) worksheets (sheets). Naming a workbook Sheet9 is more than a little confusing.

Quote:

---

Originally Posted by Pastry_chef

I get an error, so far.
Runtime error 9. Subscript out of range.
I'm asked to debug the follow code line:
With Sheets("Sheet9").Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Resize(14) 'destination sheet, to the right of data in row 3

---

Do you have a sheet called Sheet9? If not, either rename the sheet Sheet9 or adjust the code so that references to Sheet9 in the code are changes to the actual sheet name.

Thank you, very much!!!!!
It works.
I just had to change the name of the tabs without the space in between.
Thank you very much for your help!!!!
I really appreciate it!!!!!
Please let me know how to thank you!!!!
I really appreciate the time you invested in answering my question!!!!

Ok, I forgot everything how we did this.
I need something a bit different, but forgot how we did this originally, so I need to ask you again.

Suppose, I have cells where the data changes every 30 seconds.
I want to plot the current value from that cell, data point from 30 seconds ago, from 1 minute ago and 1.5 minutes ago.
Basically, to have a stacked dynamic column.
The yellow and purple columns are the current values. Data from 30-sec ago, 1 min ago and 1.5 min ago is shown in grey bars.
Basically, these grey bars are the look-back bars to see how data changed over the last 1.5 minutes.

Here is the image of what I want to do, b/c this shows it clearer.
Attachment 26246

When I click on your attachment I get: "Invalid Attachment specified."
Attaching an Excel workbook would be infinitely preferable.

Let's do this for now: I have a cell (1 cell ) in Excel that updates values every 30 seconds or so (from a live feed).
How do I plot the values from that cell in a chart vs. time (in 30 sec or 1 min increments on the x-axis).
Is it possible to do?

Code:

---

Sub blah()
With ActiveSheet
  With .Cells(Rows.Count, "C").End(xlUp).Offset(1)
    .Value = .Range("A1").Value
    .Offset(, -1).Value = Now()
  End With
  .ChartObjects("Chart 1").Chart.SetSourceData Source:=Intersect(.Range("B:C").Resize(.Rows.Count - 8).Offset(1), .Range("B2").CurrentRegion)
  If UCase(.Range("B1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:00:30"), "blah"
End With
End Sub

---

Sorry, I am not a pro. So, I inserted the code above in Developed-->Visual Basic-->Worksheet.
I saved the macro.
What do I do now? How do I see the plot on a chart, or this will plot automatically?
What do I need to do to see the plot? Sorry, not a savvy user when it comes to macros.

If you'd supplied a workbook I could have made it fit, but you refused.
I'm not going to spoon feed you.
See attached.
Cell A1 is the one which you have to set up to update.

Ok, it works, thank you, I didn't mean to come off stubborn or ungrateful. Thank you very much for your help!
I apologize if I hurt your feelings!
Thank you!!!

Hi - I have a similar live dataset that I wish to print to another sheet every minute, however, the timing is not repeatable, it will print 2 or 3 times instead of once each minute, how can I make this regular & repeatable - THANKS:

Sub vol()
With Sheets("DATA")
.Columns("B:B").Copy
.Columns("C:C").Insert Shift:=xlToRight
End With


With Sheets("LIVE").Range("B1")
.Value = Time
.NumberFormat = "h:mm:ss AM/PM"
End With


With Sheets("DATA").Range("B3:B103") 'destination sheet, bottom of column A
.Value = Sheets("LIVE").Range("B1:B100").Value
End With


Dim cell As Range
For Each cell In Sheets("RESULTS").Range("F4:F104")
If Not IsError(cell.Value) Then
If cell.Value = "Pass" Then
cell.Interior.Color = XlRgbColor.rgbRed
Beep
End If
End If 'error check
Next
If UCase(Sheets("DATA").Range("A1").Value) <> "STOP" Then Application.OnTime Now() + TimeValue("00:01:00"), "vol"
End Sub