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

Copy streaming data based on matching time.

0

column a is time from 00:01 to 23:59 b1 is time (now function) auto updating. C1 is streaming data, I need to copy C1 to column D when the times match. I want it to print at 24:00 and start all over again.

A           B             C             D

00:01    00:01        .45          .45

00:02    

00:03

00:04

00:05

Thank in advance!!!

Answer
Discuss

Answers

0
Selected Answer

I got it, tis went in workbook.

Private Sub WORKBOOK_OPEN()
    With Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
        .Value = .Parent.Range("A1").Value
        .Offset(, 1).Value = Now
    End With
    NextTime = Now + TimeValue("00:01:00")  '1 minutes
    Application.OnTime NextTime, "COPY"
    Application.OnTime TimeValue("23:59:15"), "PrintOneSheet"
    Application.OnTime TimeValue("23:59:45"), "DELETE"
End Sub
than put this in a module
Sub COPY()
    With Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
        .Value = .Parent.Range("A1").Value
        .Offset(, 1).Value = Now
    End With
    NextTime = Now + TimeValue("00:01:00")  '1 minutes
    Application.OnTime NextTime, "COPY"
End Sub
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Discuss

Discussion

Thanks for posting your solution!
don (rep: 1989) Oct 9, '16 at 12:33 pm
Add to Discussion
0

Try this:

Sub copy_stream()

Application.ScreenUpdating = False

For Each xCell In Range("A1", Range("A" & Rows.Count).End(xlUp))

    If xCell.Value = Cells(xCell.Row, xCell.Column + 1).Value Then

        Cells(xCell.Row, xCell.Column + 3).Value = Cells(xCell.Row, xCell.Column + 2).Value

    End If

Next xCell

Application.ScreenUpdating = True

End Sub
Discuss

Discussion

No dice. doesnt work, nothing being copied to column D.
jandjcomp Oct 7, '16 at 3:48 pm
You are going to have to give me more information than that. That's not helpful at all. The macro works perfectly in a test worksheet when I ran it.
don (rep: 1989) Oct 7, '16 at 3:50 pm
C1 is streaming data from dde object (link pasted into C1). i pasted your code into the sheet, saved then restarted the workbook and nothing happened. 
jandjcomp Oct 7, '16 at 4:43 pm
If i open the workbook at 2:00 in the afternoon it should start copying c1 to D841 than one min later D842
jandjcomp Oct 7, '16 at 4:55 pm
You have to make the macro run. It is not set to run automatically.
If you want it to run automatically when you open the file, follow this tutorial from us and use the Workbook_Open event. Also, this macro is set to run once on the entire sheet and if you need it to run each time the data is updated, then you need to modify the macro I included and insert it into the AutoCalculate macro that you removed from the smaple sample you uploaded.
don (rep: 1989) Oct 8, '16 at 7:18 pm
Add to Discussion


Answer the Question

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