Copy Paste values from 1 worksheet to another every 5 minutes

[SXMXM]

Hello,

I have a spreadsheet that gets live financial data. I have 3 columns (A, B & C) that are updated using some formula from this streaming data. I would like to copy the values (not formulas) of columns A & C every 5 minutes, transpose them and paste them in 2 separate worksheets (e.g. Sheet2 for Col A values and Sheet3 for Col B values).

I would also like to specify a start time and end time so that the macro does not run endlessly.

Would appreciate any help on this.

Thanks

 

[Scuderia]

Hi

I am not sure how the following macro will interact with whatever method you have for streaming the live data into your spreadsheet.
However it will copy your data as requested

-----------------------------------------------------------------------------------------------

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Sub Copy()

Application.ScreenUpdating = False

Do While Sheets("Sheet1").Range("E2") > Now()

Sleep 15000

Loop

Do While Sheets("Sheet1").Range("F2") > Now()


Sheets("Sheet1").Range("A1:A5000").Copy
Sheets("Sheet2").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Sheets("Sheet1").Range("B1:B5000").Copy
Sheets("Sheet3").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Application.CutCopyMode = False
Sheets("Sheet1").Select

Sleep 60000

Loop

Application.ScreenUpdating = True

End Sub

---------------------------------------------------------------------------------------------

Change copy/paste ranges as appropriate

Assumes
Your data is on "Sheet1"
Start time is in E2
End time is in F2

Also Once you set the macro to run you will not be able to interact with the spreadsheet until cancelled or end time reached

"Sleep 60000" equates to 1 minute for you to test. If OK change to 300000 for 5 minutes

 

[John_w]

OP, use Application.OnTime for the 5 minutes periodic copying - Pearson Software Consulting

For the copying itself, record a macro with you manually doing a copy paste with transpose to get the starting VBA code for further editing and incorporating the timer part.