#1
|
|||
|
|||
Copy one cell to another every hour
I have a macro that copies cell A1 to column I every hour.It copies A1 to I2 then A1 again to I3, I4, I5 and so on. But the list is too long. I'm only interested in the last few hours. I want the list to go from I2 to I30. So every time it should copy over a new number, it should delete the first one on I2 and move up the rest. The idea is that I will make a graph of the data.This is how the code looks. But the last code I do not know where it should be.Can anyone help me please?
Sentiment.xlsm Option Explicit Public dTime As Date Sub ValueStore() Dim dTime As Date Range("I" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value Call StartTimer End Sub Sub StartTimer() dTime = Now + TimeValue("01:00:00") Application.OnTime dTime, "ValueStore", Schedule:=True End Sub Sub StopTimer() On Error Resume Next Application.OnTime dTime, "ValueStore", Schedule:=False End Sub __________________________________________________ _____________________________ Private Sub Worksheet_Change(ByVal Target As Range) Dim NR As Long If Not Intersect(Target, Range("A1")) Is Nothing Then NR = Range("I" & Cells(Rows.Count).Row).End(xlUp).Row + 1 Range("I" & NR).Value = Range("A1").Value If NR > 30 Then Range("I2").Delete xlShiftUp End If End Sub |
#2
|
|||
|
|||
Quote:
It will re-write I2:I30 every time A1 changes, not hourly. And that is actually dependent on how A1 receives its value. If there's a formula in A1, the Worksheet_Change event will never trigger, because what's in the cell (ie: the formula) never changes even though the displayed result does. Last edited by NoSparks; 02-21-2019 at 01:28 PM. |
#3
|
||||
|
||||
try this as the entire Modul1 code:
Code:
Option Explicit Public dTime As Date Sub ValueStore() Dim NR As Long NR = Range("I" & Cells(Rows.Count).Row).End(xlUp).Row + 1 Range("I" & NR).Value = Range("A1").Value If NR > 30 Then Range("I2").Delete xlShiftUp Sheets("Blad1").ChartObjects("Diagram 4").Chart.FullSeriesCollection(1).Values = "=Blad1!$I$2:$I$30" End If Call StartTimer End Sub Sub StartTimer() dTime = Now + TimeValue("01:00:00") Application.OnTime dTime, "ValueStore", Schedule:=True End Sub Sub StopTimer() On Error Resume Next Application.OnTime dTime, "ValueStore", Schedule:=False End Sub Your Start Timer button would be better calling ValueStore and not StartTimer, then you don't have to wait an hour for the first new value to appear. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy matching data from one report cell to another cell report | mbesspiata | Excel | 9 | 01-15-2015 01:49 PM |
Copy Rows down with value from another Cell | burlinghamla | Excel | 1 | 01-14-2015 06:26 AM |
keyboard shortcut to copy the url of a cell | Aquinax | Excel | 3 | 07-15-2014 09:45 AM |
Copy Paste each cell to a new page | Singh_Edm | Word | 12 | 01-20-2014 12:55 AM |
Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell? | tatihulot | Excel | 4 | 05-16-2013 04:30 PM |