Copying data from one column to another at periodic interval

[ankurm84]

Hi...

I have data in column(A) which get updated after a minute. Values in this column(A) are derived values.

What I want is , that data from column (A) will get copied to column(B) after minute and after 2 min, updated value in column(A) should get copied in column(C) and so forth.

I have very limited understanding of VBA.

Tried to follow below thread:
https://www.mrexcel.com/forum/excel-questions/379070-simple-copy-update-cell-value-every-minute.html

but not able to achieve what I am looking for.

This particular code i am using currently, is copying the values , but along with formula used in column A, because of which after 2 mins value in column A,B & C become same:

Option ExplicitPublic dTime As Date


Sub ValueStore()
Dim iCol As Long
Columns(3).Copy Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
Call StartTimer
End Sub






Sub StartTimer()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub


Sub StopTimer()
On Error Resume Next
Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

Any help is greatly appreciated!!

Thanks

 

[jmacleary]

Hi there. Replace your ValueStore subroutine with this:

Sub ValueStore()
Dim iCol As Long
Columns(1).Copy
Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

Call StartTimer
End Sub

Regards
John

 

[ankurm84]

Thanks a lot John...It worked
But somehow my STOP button which uses below sub, has stopped working...any idea why?

Sub StopTimer()
On Error Resume Next
Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

Thanks,
Ankur

 

[jmacleary]

Make sure youve got all the code in one module, and the Public dTime As Date declaration is at the top of it. It is also possible that if you use the STOP button when ValueStore is executing the stop will be ignored.

 

[ankurm84]

Thanks John. It is working now.

 

[jmacleary]

You are most welcome.