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!

 

[henryvii99]

Also, is it possible to define the time for the macro to start and stop?
i.e. Start at 10 am in the morning and stop the update in 4pm?

 

[henryvii99]

I had completed the copying part, anyone knows how to add a time refresh to the code?
Thanks a lot

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LC As Long
If Not Intersect(Target, Range("C204")) Is Nothing Then
    Application.EnableEvents = False
    Range("C4:C204").Copy Destination:=Range("D4:D204")
    Application.EnableEvents = True
End If
End Sub

 

[henryvii99]

How can I include
Application.OnTime Now + TimeValue("00:00:15"), "Worksheet_Change"
to my workbook? I think it may be a solution...
but it shows an error message after the countdown!

"Cannot run the macro "Book1!Worksheet_Change". The macro may not available to this workbook or all macros have been disabled.

 

[chuckchuckit]

Henry, Here is how to get a macro to start at 10AM. You first have to run this macro below anytime "before" 10AM. It will then run your macro at 10AM.

Sub SetupOurStartTime()
    Dim TheTimeWeStart
    TheTimeWeStart = "10:00:00"
 
    Application.OnTime TimeValue(TheTimeWeStart), "YourMacroNameToRunAtTenAM"
End Sub

I do not think you can call any "Worksheet_Change" type macro from this code, because "Worksheet_Change" events are already basically running once you have opened the workbook. They are just looking for your target range for them to start their code. So you would have no need to turn them on, and may be no way to turn them off either.

If you are perhaps looking for a way to have "Worksheet_Change" code be off until your 10AM start time, a way to do that in a sense might be to make your target range a global variable set to some not used range. Then when the 10AM macro code triggers, it could assign the proper range to that global target for the "Worksheet_Change" at that time. Then your "Worksheet_Change" target range in effect would be turned on at 10AM.

Hope that is of assistance.

 

[Cindy Ellis]

Building on the start time macro...the following will copy the contents of C4:C204 every X seconds, with X read from cell B4. If you set B4 to 0, it will stop the macro (which you really might want to do from time to time.)
This goes into a standard module, not the worksheet module:

Sub CopyC2D()

    Range("C4:C204").Copy Destination:=Range("D4:D204")
    Call CopyTime
End Sub

Sub CopyTime()
If Range("B4").Value > 0 Then
Application.OnTime Now + TimeValue("00:00:" & Range("B4").Value), "CopyC2D"
End If
End Sub

In the macro from ChuckChuckit, change "YourMacroNameToRunAtTenAM" to "CopyC2D"

To make everything play nicely together, you should add specific workbook and worksheet names to the macro so that you can use Excel for other things while this is running along on its own in the background.

To get it started, run enter a value > 0 in Cell B4, and run the SetupOurStartTime macro.

I'm still working on how to make it stop at 4PM...my date/time arithmetic isn't doing what I want it to yet. I'll post back when I figure out what I'm doing wrong.

 

[Cindy Ellis]

OK...this goes in a regular module. You shouldn't need the "setupourstarttime" macro, since this checks for time of day and runs as long as B4 is greater than 0 and it's between 10AM and 4PM.

Sub CopyC2D()
Dim TimeOfDay As Double
    TimeOfDay = (Now() - Application.WorksheetFunction.RoundDown(Now(), 0))
    If TimeOfDay > 10 / 24 And TimeOfDay < 16 / 24 Then '10/24 is 10AM, 16/24 is 4PM
        Range("C4:C204").Copy Destination:=Range("D4:D204")
    End If
    Call CopyTime
 
End Sub
Sub CopyTime()
If Range("B4").Value > 0 Then
Application.OnTime Now + TimeValue("00:00:" & Range("B4").Value), "CopyC2D"
End If
End Sub

Start it any time using CopyC2D. If the current time is between 10AM and 4PM, the macro copies C4:C204 to column D, at the interval controlled by B4.
Hope this helps,
Cindy

 

[chuckchuckit]

Was thinking he would have to run an other macro to turn off at 4PM but looks like you solved that problem if your code is always running once CopyC2D is called.

It seems from his post #4 he also was wanting to start everything with a "Worksheet_change" event with (Target, Range("D322")) if I am understanding his questions.

 

[Cindy Ellis]

I think using Worksheet_Change was just the way it was working before.
Here's a slightly modified version that specifies the workbook and worksheet so that it keeps its place even if other Excel workbooks are open or active:

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 < 16 / 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)

    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

 

[chuckchuckit]

Might be missing the Sub CopyC2D () line
-but am getting a subscript out of range on the "With Workbooks ... line of top function