Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-21-2019, 03:33 AM
Crillezzz Crillezzz is offline Copy one cell to another every hour Windows 10 Copy one cell to another every hour Office 2007
Novice
Copy one cell to another every hour
 
Join Date: Feb 2019
Posts: 2
Crillezzz is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 02-21-2019, 05:41 AM
NoSparks NoSparks is offline Copy one cell to another every hour Windows 7 64bit Copy one cell to another every hour Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
But the last code I do not know where it should be.
Worksheet_Change is a worksheet event and must be in the sheet module not a standard module.
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.
Reply With Quote
  #3  
Old 02-22-2019, 10:47 AM
p45cal's Avatar
p45cal p45cal is offline Copy one cell to another every hour Windows 10 Copy one cell to another every hour Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
No Worksheet_Change event needed anywhere.


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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy one cell to another every hour Copy matching data from one report cell to another cell report mbesspiata Excel 9 01-15-2015 01:49 PM
Copy one cell to another every hour Copy Rows down with value from another Cell burlinghamla Excel 1 01-14-2015 06:26 AM
Copy one cell to another every hour keyboard shortcut to copy the url of a cell Aquinax Excel 3 07-15-2014 09:45 AM
Copy one cell to another every hour 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft