Hey guys,

I'm fairlly new to VBA programming. I want an updating field to be copied into the next empty column every 4 minutes and was wondering if anyone had an idea to do so, with the least amount of processing power to do so. At the same time, I want a time stamp to be inserted above the column.

At first I used "end.Xl" and copy/paste-special, but that was quite consuming of data power.

Preferably it should go to one worksheet to another, without automatic screen updates and such. This is the code I've come up with so far:


Option Explicit
Dim vWhen As Variant
Public col As Integer


 Sub MD()
    Dim LC As Double

    Application.ScreenUpdating = False
    Dim workbook1 As Workbook
    Set workbook1 = Workbooks("Final.xlsm")
    workbook1.Activate
    Dim ws1, ws2 As Worksheet
    Set ws1 = Sheets("ICAP")
    Set ws2 = Sheets("Data")
    Dim i As Long
    i = 4
    ws1.Cells(3, col) = Now()
        ws1.Cells(i, col) = ws2.Cells(i, 4)
        i = i + 1
    col = col + 1
    ws1.Cells(1, 5) = col
    
    Application.ScreenUpdating = True
    
End Sub
I also tried to get it run at 8:30 every morning, every 4 minutes, until 17:00 but seem to get it to work. Much appreciated if anyone could help me with this code as well.

Sub Morning()
Application.OnTime TimeValue("08:30:00"), "MD", TimeValue("17:00:00"), TimeValue("00:04:00")
End Sub
Kind regards,
pippinsmckey