Nearly There?

[Saxman]

With the grateful help of Chip Pearson, I have managed to assemble the
following code with a 'timer' event. Basically, I need to copy data from
the "Show" worksheet 15 times with a 30 second delay and paste into the
"Chartdata" worksheet. This it does successfully when I run the "The Sub"
routine, but there is no 30 sec. delay in each copy/paste routine. Is this
because every copy/paste operation requires sub routine name? I cannot use
'The Sub' name repeatedly, as VB sees it as a duplicate.

I cannot get the 'Stop Timer' routine to work when I run it neither.
Someting must be amiss.
..........................................................................
Public RunWhen As Double
Public Const cRunIntervalSeconds = 30 '30 seconds
Public Const cRunWhat = "The_Sub"
Sub StartTimer()
.........................................................................
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
...........................................................
Sub The_Sub()
StartTimer
Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("L2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Show").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Chartdata").Select
Range("P2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
...........................................................................
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

 

[Tom Ogilvy]

If you execute StartTimer, then your code should run in 30 seconds and every
30 seconds thereafter.
I don't see anything obviously wrong with it. If it is taking more than 30
seconds to do the copy, then that would be problematic. Also, OnTime is not
preemptive. It will not interrupt Excel if it is busy.

Try stripping out the copy and paste code and just have the bear bones code.
Get all the procedures running. then start adding back in the code to do
the copying. Just note, you can copy without all the selecting a screen
activity. I can't tell what you are copying from worksheets Show as you
never select anything, so it appears you are copying the same thing over and
over - like I say, can't tell.

Worksheets("Show").Range("A1:A10").Copy
worksheets("ChartData").Range("B2").PasteSpecial xlValues

If your intent is is to copy the same location in successive columns at 30
second intervals, then as written, you code won't do that. Remove all that
code and try


set rng = worksheets("ChartData").Range("IV2").End(xltoLeft)
if rng.column < 2 then _
set rng = Worksheets("ChartData").Range("B2")
Worksheets("Show").Range("A1:A10").Copy
rng.pastespecial xlValues

or post back with specifics.

 

[Saxman]

I can't tell what you are copying from worksheets Show as you
never select anything, so it appears you are copying the same thing over and
over - like I say, can't tell.

It does copy the same range everytime, but the data is refreshed every 30
seconds from the web. I need to paste into successive columns so that the
data can be read by a 3D graph and progressions monitored.

If your intent is is to copy the same location in successive columns at 30
second intervals, then as written, you code won't do that.

That was my intention. So far, I can download the data to ("Show") and get
it refreshed at a chosen interval; also I have the graph cells formatted
within ("Chartdata"), which is read by another worksheet ("Graph") (where
the graph is placed). I need to link the worksheets ("Show")/("Chartdata")
via copy/paste at a set interval by the user (which would normally be the
same time interval for gathering data onto ("Show")).

Remove all that code and try......

I will give it a go.

Thanks!

 

[PY & Associates]

Appears whether we start with The_Sub or StartTimer,
the moment The_Sub is run, there is no mechanism to pause please

 

[PY & Associates]

Try putting either of the following inbetween each copy and paste cycle

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30
Application.Wait TimeSerial(0, 0, 30)
You can get rid of the starttimer and stoptime subroutines.
Don't think you need that.

 

[Saxman]

Try putting either of the following inbetween each copy and paste cycle

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30
Application.Wait TimeSerial(0, 0, 30)
You can get rid of the starttimer and stoptime subroutines.
Don't think you need that.

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30

The above causes a syntax error and this-

Application.Wait TimeSerial(0, 0, 30)

.....pastes the first column and then hangs, even if I eliminate the
starttimer and stoptime subroutines.

There must be a simpler way of delaying an operation such as mine?

Could a timer be placed in Excel on a hidden form?

Something like this:-

Private Sub Form-Load()
Form1.Show
Timer1.Interval = 30000 '30 seconds
Timer1.Enabled = True
End Sub

I'm afraid that I am not very hot on VB, but I know the solution has got to
be simple? That is why I haven't given up yet.

 

[Tom Ogilvy]

look at the help example for Wait:

If Application.Wait(Now + TimeValue("0:00:10")) Then
MsgBox "Time expired"
End If


You don't have to use the if statement.

Application.Wait(Now + TimeValue("0:00:30"))

 

[Saxman]

look at the help example for Wait:

If Application.Wait(Now + TimeValue("0:00:10")) Then
MsgBox "Time expired"
End If


You don't have to use the if statement.

Application.Wait(Now + TimeValue("0:00:30"))

This works perfectly, but I now have to find a way of ending the loop, as
it goes back to the 1st copy/paste routine.

I have not included the MsgBox code yet, but that shouldn't make any
difference?

Thanks to you, I have overcome the hardest bit.

 

[Saxman]

Try putting either of the following inbetween each copy and paste cycle

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30
Application.Wait TimeSerial(0, 0, 30)
You can get rid of the starttimer and stoptime subroutines.
Don't think you need that.

This should read:-
Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now())+30)
Application.Wait TimeSerial(0, 0, 30)

Thanks for the mailing which I accidentally deleted. The above still
copy/pastes the first column and then hangs.

This works better as stated previously.

Application.Wait(Now + TimeValue("0:00:30"))