Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Time to copy a set of Cell

0

I got a set of cell in one column (B2 to B15), this contain dynamic variabe data.

I got another column, and I title it as a time stamp E1,  (hh:mm:ss), let say 06:30:00 and

below ( E2 to E15) it is where I  gonna  paste what I copy data on  B2 to B15.

My condition is when the real time is  06:30:00 , it will compare to my title

on E1 (since it is a time stamp) and if is true it will proceed in copying data and pasting

it.to E2 to E15..

Any clue how I could do it.

Answer
Discuss

Answers

0
Selected Answer

Hi Rene and welcome to the Forum

I assume you want to copy all data from B to E when the time equals the time in E1 (06:30 or whatever). 

In the attached workbook, I've modified code from Don's tutorial Countdown Timer in Excel to create a clock.

In column M you'll see two buttons- "Start timer" starts that clock (by running macro SetTime below). That puts the current time in cell M1 and (every second) calls the sub CheckTime which sees if the cuurent time matches E1 (provided there;s a value there). If so, it copies B2:B15 values to E2 to E15 and puts a comment in F1 to say it happened. It also beeps. If not, it does nothing but SetTime gets triggered after another second (and the random values in B2:B15 change- to emulate your varying values).

The code below is commented for your guidance:

Dim SchedRecalc As Date

Sub SetTime()

CheckTime 'see if action is needed
' set next time, locking to nearest second
SchedRecalc = WorksheetFunction.MRound(Time, TimeValue("00:00:01")) + TimeValue("00:00:01")

Application.OnTime SchedRecalc, "SetTime" 'retrigger this macro after a second
Sheet1.Range("M1").Value = Time 'update time in A1

End Sub

Sub Disable()

On Error Resume Next

Application.OnTime EarliestTime:=SchedRecalc, Procedure:="SetTime", Schedule:=False

End Sub

Sub Reset()
Call Disable 'stop timer
Sheet1.Range("M1").Value = "" 'clear time
Sheet1.Range("F1").Value = "" 'clear last copy comment
End Sub

Sub CheckTime()

With Sheet1.Range("E1")
    'Check if current time matches E1 (with 0.5 seconds)
    If IsEmpty(.Value) = False And _
    Time >= .Value And _
    Time < (.Value + TimeValue("00:00:01") / 2) Then
        'if so,copy values
        Sheet1.Range("E2:E15").Value = Sheet1.Range("B2:B15").Value
        ' add a comment in F1
        .Offset(0, 1).Value = "Last copied: " & Now
        ' make a sound
        Beep
    End If
End With

End Sub
The button "Stop timer" stops the clock and clears the comment in F1.

You can change the value in E1 at any time (for test purposes)

Hope this is what you wanted (or you can modify it to do what you want).

Discuss

Discussion

Thank You John.
Now I have to implement it in macro, problem is I am new to macro.
I need to go to your You Tube channel and learn fast.
But this thing help a lot, coz it will save me time  from copying and pasting.
I will keep you inform about my progress, and hope to go back more to ask question.

Rene
rene6985 (rep: 2) Dec 21, '21 at 11:27 am
Rene

The macro is in the file attached to my Answer and the process should work. If it does then I think answered your question and -according to the Forum's Rules (above)- you should mark the Answer as "Selected".

The code lives in a module which will be visible in VB Project Explorer and can be copied to another open workbook. If you edit your original question and use the Add Files... button to attach your workbook, I can do that for you (and check sheet references work).
John_Ru (rep: 6092) Dec 21, '21 at 11:54 am
Still trying to make it work.
rene6985 (rep: 2) Dec 21, '21 at 11:58 pm
Send me your file (as described above) and I'll modify it later then re-post
John_Ru (rep: 6092) Dec 22, '21 at 1:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login