Hello, I am using Excel 2000 and I am trying to accomplish the following: I want to copy the value from Sheet "Tickers" (which is always changing) and paste it every second on Sheet "Data". I want to start pasting at cell L3, move to the right one cell, paste again, and so on until the minute is up. When the next minute starts, I want to paste directly below, starting at L4. When completed, each row will give me a per-second recording of what happened, each row being one minute. I also want to print the time before each row, in column K. (This code is in 'Module 2')
I am a real newb so my code might be really screwed up. I can't get it to run, it gives me the following error: "Run-time error '1004': Application defined or object-defined error" And it goes to the line I have marked below with an asterisk. Can someone take a look at this code and tell me what is wrong?
I also wanted the code to start at 2:30AM and stop at 3:00PM if the workbook is open, but I don't know how to accomplish that.
Any help is appreciated - code pasted below:
----------------------------------
Sub CaptureRefresh()
Dim Count As Integer
'Starts "Last" at L2 and prints time
Last = Sheets("Data").Range("L3")
Sheets("Data").Range("k3") = Time
'Starts the count at one
Count = 1
'Starts the Copyrow procedure
Call CopyRow
End Sub
--------------------------------------
Sub CopyRow()
'Copies price from the ticker sheet
Sheets("Tickers").Range("N25").Copy
'Pastes price at "Last" location and moves over one
* Sheets("Data").Range(Last).offset(1, 0).PasteSpecial Paste:=xlValues
'Says that Nextemps starts in one second
NextTemps = Now + TimeValue("00:00:01")
'Adds one to the second counter
Count = Count + 1
'If the minute is not yet up, do the procedure again
If Count < 61 Then
Application.OnTime NextTemps, "CopyRow"
'If the minute is up, return "Last" to row below
'reset the second counter
'and do the procedure again
Else
Set Last = Last.offset(-60, 1)
Count = 1
Application.OnTime NextTemps, "CopyRow"
End If
End Sub
I am a real newb so my code might be really screwed up. I can't get it to run, it gives me the following error: "Run-time error '1004': Application defined or object-defined error" And it goes to the line I have marked below with an asterisk. Can someone take a look at this code and tell me what is wrong?
I also wanted the code to start at 2:30AM and stop at 3:00PM if the workbook is open, but I don't know how to accomplish that.
Any help is appreciated - code pasted below:
----------------------------------
Sub CaptureRefresh()
Dim Count As Integer
'Starts "Last" at L2 and prints time
Last = Sheets("Data").Range("L3")
Sheets("Data").Range("k3") = Time
'Starts the count at one
Count = 1
'Starts the Copyrow procedure
Call CopyRow
End Sub
--------------------------------------
Sub CopyRow()
'Copies price from the ticker sheet
Sheets("Tickers").Range("N25").Copy
'Pastes price at "Last" location and moves over one
* Sheets("Data").Range(Last).offset(1, 0).PasteSpecial Paste:=xlValues
'Says that Nextemps starts in one second
NextTemps = Now + TimeValue("00:00:01")
'Adds one to the second counter
Count = Count + 1
'If the minute is not yet up, do the procedure again
If Count < 61 Then
Application.OnTime NextTemps, "CopyRow"
'If the minute is up, return "Last" to row below
'reset the second counter
'and do the procedure again
Else
Set Last = Last.offset(-60, 1)
Count = 1
Application.OnTime NextTemps, "CopyRow"
End If
End Sub