VBA Code to link with Time Clock within Excel Sheet instead of my PC Clock

Hi,

I have a code which drags cells containing formulas one cell down after every one minute.
This code takes Time from my PC clock.

Now I want to make a change In this code
In cell D1 I have DDE server clock time which is updating live.
I only want that you just change this code and link this code with clock time in Cell D1 instead of my PC clock.

I would think you just need to change

 Tm = Now

to

 Tm = Range("D1").Value2

You might need to qualify the range with the sheet name.
For example

 Tm = Sheet("Sheet1").Range("D1").Value2

Hi, @TMS

I am new to Excel and does not know anything about coding.

Can you please sir add these changes in the code and reply me in this thread with full changed code in it.

Thanks a lot

I tried and this is result i got. But when i click F5 to run file just got hanged every time.

Option Explicit

Dim dtNextTick      As Date


Sub StartTimer()
    Dim lSec        As Long
    Dim Tm          As Date

    Tm = Range("D1").Value2
    lSec = Second(Tm)

    dtNextTick = Tm + TimeSerial(0, 0, 60 - lSec)

    Application.OnTime dtNextTick, "UpdateData"
End Sub


Sub UpdateData()
    Dim lRow        As Long
    Dim lRowB       As Long

    Debug.Print Time & " <> " & dtNextTick

    With ThisWorkbook.Worksheets("Sheet1")
        lRow = .Cells(.Rows.Count, "J").End(xlUp).Row
        lRowB = .Cells(.Rows.Count, "B").End(xlUp).Row

        If lRow < lRowB Then
            Call StartTimer

            .Cells(lRow + 1, "J").FormulaR1C1 = .Cells(lRow, "J").FormulaR1C1
            .Cells(lRow + 1, "L").Resize(, 2).FormulaR1C1 = .Cells(lRow, "L").Resize(, 2).FormulaR1C1
            .Cells(lRow + 1, "R").Resize(, 2).FormulaR1C1 = .Cells(lRow, "R").Resize(, 2).FormulaR1C1


            .Cells(lRow, "J").Value = .Cells(lRow, "J").Value
            .Cells(lRow, "L").Resize(, 2).Value = .Cells(lRow, "L").Resize(, 2).Value
            .Cells(lRow, "R").Resize(, 2).Value = .Cells(lRow, "R").Resize(, 2).Value


        End If

    End With
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime dtNextTick, "UpdateData", , False
    On Error GoTo 0
End Sub

'code also in ThisWorkbook module

You have made the change as I would have. It might be worth trying the fully qualified version.

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)