Copy column C to column D every X seconds

[henryvii99]

Dear everyone,

I have a macro which can copy the data from column D to column L and shift the existing data to the next column by the following script,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LC As Long
If Not Intersect(Target, Range("D322")) Is Nothing Then
    Application.EnableEvents = False
    LC = Cells(4, Columns.Count).End(xlToLeft).Column + 1
    Range("D4:D322").Copy Destination:=Cells(4, LC)
    Application.EnableEvents = True
End If
End Sub

This time, I just to copy the value from C4:C204 to D4:204 every x seconds, without the need to shift the data.

But I want to control the update time by the input in column B, e.g. when B4 is 45, I want the cell to be copied from C4 to D4 every 45 seconds.

Many thanks in advance!

 

[chuckchuckit]

Got it to work:

Made line 4 Sub CopyC2D(), and had to remove ".xlsx" from 5th line for some reason.

Then when adding in WB and WS names works nice!

 

[Cindy Ellis]

Copy-paste error!! Here's the whole thing...

Sub CopyC2D()
Dim TimeOfDay As Double
Dim MyWB As String
Dim MyWS As String
    MyWB = "YourWorkBookNameHere.xlsx"
    MyWS = "YourWorkSheetNameHere"
    TimeOfDay = (Now() - Application.WorksheetFunction.RoundDown(Now(), 0))
    With Workbooks(MyWB).Worksheets(MyWS)
        If TimeOfDay > 10 / 24 And TimeOfDay < 22 / 24 Then
            .Range("C4:C204").Copy Destination:=.Range("D4:D204")
        End If
    End With
    Call CopyTime(MyWB, MyWS)
    
End Sub
Sub CopyTime(WB As String, WS As String)
Dim MyWorkbook As String
Dim MyWorksheet As String
    With Workbooks(WB).Worksheets(WS)
        If .Range("B4").Value > 0 Then
            Application.OnTime Now + TimeValue("00:00:" & .Range("B4").Value), "CopyC2D"
        End If
    End With
End Sub

 

[Cindy Ellis]

The post above has the wrong "stop time"...I had to set it later for testing and forgot to set it back.
In the line

 If TimeOfDay > 10 / 24 And TimeOfDay < 22 / 24 Then

Change "22 / 24" to "16 / 24" for 4PM.
(Note that this is based on the way Excel stores time, as the fraction of a day).

 

[henryvii99]

Thanks for everyone's help

I love you all