Macro to Archive Data in new sheet

[micgee]

I have an excel worksheet (Sheet 1) thats shows the amount of electricity used over an entire 8 hour shift. The electricity is monitored and updated every 1 min so Sheet 1 also refreshes every min (60 sec) to update to the new electricity usage. Sheet 1 refreshes entirely at the beginning of a new shift, starting with 0 data points and growing every min as the shift continues.

In Sheet 1, there is a box that displays the total electricity usage for the shift. For every shift, the value is shown in the same box (F3) . Because the sheet refreshes with every shift, I lose that information.

I want to create a macro to store and save the end-of-shift total electricity usage value (F3) in a new sheet (Sheet 2) and move down a row to store the value at the end of the next shift (also F3 but with a new shift value), and so on.....

I would also like to time stamp it with the end-of-shift time that can be found on another sheet. This time sheet is the control for how often Sheet 1 is refreshed.

 

[JoeMo]

Might be easier to include the archiving/date stamping into whatever code is initiating the refresh of Sheet1 at the end of a shift - or is that 'refresh' done manually?

 

[micgee]

Might be easier to include the archiving/date stamping into whatever code is initiating the refresh of Sheet1 at the end of a shift - or is that 'refresh' done manually?

Thanks for that thought JoeMo!

The code initiating the refresh is as follows:

Sub CheckIt()

Dim MyNow As Date
Dim ShiftBeg As Date
Dim ShiftEnd As Date
Dim Shifttest As Date

MyNow = Range("C10")
ShiftBeg = Range("C5")
ShiftEnd = Range("C6")
Shifttest = ShiftEnd + 0.04166667
If MyNow > ShiftEnd Then
If MyNow > Shifttest Then
ShiftBeg = ShiftBeg + 0.5
Range("C5") = ShiftBeg
'MsgBox ("Date Changed")
End If
End If

'MsgBox ("Compare Complete")


End Sub


The code I have currently to archive the cell value without the time stamping is as follows:

Sub t()
Dim Ary As Variant
With Sheets("Display")
Ary = Array(.Range("F4"), .Range("H4"))
End With
For i = LBound(Ary) To UBound(Ary)
If i = LBound(Ary) Then
Sheets("Archive").Cells(Rows.Count, 1).End(xlUp)(2) = Ary(i)
Else
Sheets("Archive").Cells(Rows.Count, 1).End(xlUp).Offset(0, i) = Ary(i)
End If
Next
End Sub


Do you have any suggestions on how to write the archiving code into the time code?
I want it to archive the value in cell F4 and H4 in the sheet "Archive" when the time code reaches MyNow = ShiftEnd. I would like the columns in "Archive" to go ShiftEnd Value, F4 Value, H4 Value