Need help - copy a value and paste every second

6

65Matt

New Member
Joined
Dec 17, 2004
Messages
5
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
I am a VBA less-than-knowledgeable person. Still, my sense of symmetry makes me think that, in your line:

* Sheets("Data").Range(Last).offset(1, 0).PasteSpecial Paste:=xlValues

your Range(Last) should probably be Range("Last"). Have you tried this yet?
 
Upvote 0
I believe that you don't have "Last" declared in the right place, as it doesn't appear to be public. Try this
Code: 
Sub CopyRow()
    Dim Last As Object
        Set Last = Sheets("Data").Range("L3")
        
'Copies price from the ticker sheet
Sheets("Tickers").Range("N25").Copy

'Pastes price at "Last" location and moves over one
Last.Offset(1, 0).PasteSpecial Paste:=xlValues
Hope that helps,

Smitty

[Merry Christmas Ralph! We'll be down your way next week...Ft. Worth, Victoria, Houston...]

EDIT: Silly me, Welcome to the Board!
 
Upvote 0
Howdy fellas,
Ralph's sense of symmetry has served you well, for that range should indeed have quotes around it. But... I think you'll find (even with that fixed) what you'll get is the cell L4 being constantly updated, instead of across row 3.
(Check it out:)
Last = Sheets("Data").Range("L3")
and then
Sheets("Data").Range("Last").offset(1, 0).PasteSpecial Paste:=xlValues This refers to the cell one cell below the range named "Last" (in your case cell L3)

For what you've tried, a method that would be better would be this:
Sheets("Data").Range("Last").End(xlToRight).Offset(, 1).Value = Sheets("Tickers").Range("N25").Value

HOWEVER...this isn't what you will want either. This requires some value to be in, not only the range Last, but in the cell to the right of it also before it will work. (From that point it would work great.... until the end of the minute.)

If it were me, I would think about a worksheet calculate or worksheet change event tied to your Tickers sheet, that whenever N25 changes it updates the Data sheet. (Which event to use depends on if the value in Sheet("Tickers").[N25] gets updated by a formula or by getting pasted from somewhere else.)

I hope this helps,
Dan

Edit:
Heya Smitty! Didn't see you there before I started slow-typing my long winded response. Have a good trip and "Happy Festivus!"
 
Upvote 0
I forgot to mention the auto starting of your routine at 2:30 AM and stopping it at 3:00 PM.

You can achieve this by using the Application.OnTime command. This does not come without a little confusion.
For example, you could use this in your Workbook_Open event (in the ThisWorkbook module):
Code: 
Private Sub Workbook_Open()
Application.OnTime TimeValue("02:30:00"), "CaptureRefresh"
End Sub
This *should* stop it at 3:00 PM (not tested on my part though)
Code: 
Application.OnTime TimeValue("15:00:00"), "CaptureRefresh",,False

Some of the "problems" this can cause though is that as long as Excel is open it will run. (ie. if Excel is not open, it won't work. AND, if Excel is open but the workbook is not, it will open up your workbook in order to run. So, in order to turn this off when you close the workbook, you can put this in the ThisWorkbook module also.
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime TimeValue("02:30:00"), "CaptureRefresh", , False
End Sub
This will pevent the workbook from opening and executing the code if the workbook has been closed.

Hope this helps,
Dan
 
Upvote 0
Here's my 2 cents.
I wrote two functions

The 1st function increments the seconds and minutes appropriately before calling the second function.

The 2nd function Records the current value of N25 on the Ticker Sheet in the appropriate Cell of sheet Data, based on current values of the minutes and seconds. It then waits 1 second before calling the 1st function, and the process repeats itself. (as long as Cell I25 of the Data Sheet is True)

First Function:
Code: 
Sub IncrementTimer()
Dim iCurrentSec As Integer, iCurrentMin As Integer, iDummy As Integer
If Worksheets("Data").Range("I5") Then 'Cell A5 must be True or False
    iCurrentSec = Worksheets("Data").Range("I3")  'Current Second is stored here
    iCurrentMin = Worksheets("Data").Range("H3")  'Current Minute is stored here
    If iCurrentSec >= 59 Then   'Start a New Minute
       iCurrentSec = 0          'Reset Seconds to 0
       iDummy = iCurrentMin
       iCurrentMin = iDummy + 1 'Increment the Minutes by 1
       ' Write the Minute in Column K
       Worksheets("Data").Cells(iCurrentMin + 3, 11) = iCurrentMin & " Minutes"
    Else 'Otherwise just increment the seconds by 1
       iDummy = iCurrentSec
       iCurrentSec = iDummy + 1
    End If
    Worksheets("Data").Range("I3") = iCurrentSec 'Write Current Second to sheet
    Worksheets("Data").Range("H3") = iCurrentMin 'Write the Current Minute to Sheet
    'Call Function to Write Current Value to the Appropriate Cell
    Call RecordCurrentValue(iCurrentSec, iCurrentMin)
End If
End Sub

2nd Function:
Code: 
Sub RecordCurrentValue(iSec As Integer, iMin As Integer)
    'Write the current value of N25 on the Tickers Sheet to the Appropriate Cell on the Data Sheet
    Sheets("Data").Cells(iMin + 3, iSec + 12) = Sheets("Tickers").Range("N25")
    'Wait 1 second then call the increment function to increment the seconds in minutes appropriately
    Application.OnTime Now + TimeValue("00:00:01"), "IncrementTimer"
End Sub

I initially called the first function using a command button. (you could use the workbook open or some other event, I believe these 2 functions will only work if the workbook is open?)

I put the Now() function in Cell N25 of the Ticker Sheet and here is what resulted when I called the first function:
Book1
HIJKLMNO
1ElapsedTime
2MinutesSeconds0Sec1Sec2Sec3Sec
35148:31:40PM8:31:41PM8:31:42PM
41Minutes8:32:40PM8:32:41PM8:32:42PM8:32:43PM
5KeepGoing?TRUE2Minutes8:33:40PM8:33:41PM8:33:42PM8:33:43PM
63Minutes8:34:40PM8:34:41PM8:34:42PM8:34:43PM
74Minutes8:35:40PM8:35:41PM8:35:42PM8:35:43PM
Data


You could probably take care of your Start and End requirements by making I5 of the data sheet True if it is within your desired time range, and false if it is not.
 
Upvote 0
You must log in or register to reply here.

Similar threads

corentint
  • Solved
A recursive procedure
Replies
3
Views
120
John_w
J
N
  • Solved
Copy/paste a row from within a loop, before that loop moves on to next cell
Replies
5
Views
225
Joe4
Joe4
F
  • Question
VBA Copy Range from 1 workbook to another if row contains a certain word
Replies
0
Views
519
floggingmolly
F
C
  • Question
Cut a row from "sheet 1" and paste onto "sheet2", run-time error '1004' "Paste method of Worksheet class failed"
2
Replies
10
Views
694
rpaulson
rpaulson
L
  • Question
VBA ERROR: Cannot Paste the Selected Copied Content
Replies
7
Views
180
Joe4
Joe4

Forum statistics

Threads
1,214,618
Messages
6,120,544
Members
448,970
Latest member
kennimack

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top