VBA- error

[LRSD781]

Hello Everyone,
I'm new to VBA macros and need some help.
I have "Live Data" through a OPC/DDE link populating in Cell A10. (connected to a PLC through RSLinx)
This data is "dynamic" and updates/changes periodically.
I'm copying the data from A10 and pasting it into C10 (pasted data is pasted as a "static number"
Ten seconds later it pastes the updated data from A10 into C11 and so on...
when pasting into C22, the data in column C clears and pastes once more into C10.
"trending data from C10-C21 for a chart" (dashboard).

Everything works in this code except that when C22 triggers the clear contents funtion
my code errors.

code:

 Dim TimeToRun

Sub auto_open()
    Call Movedata
End Sub


Sub Movedata()
    TimeToRun = Now + TimeValue("00:00:10")
    Application.OnTime TimeToRun, "Data"
End Sub


Sub Data()
     Dim last As Long
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    last = Application.WorksheetFunction.Max(10, Cells(Application.Rows.Count, "C").End(xlUp).Row + 1)
    If last <= 21 Then
      Range("A10").Copy
      Range("C" & last).PasteSpecial xlPasteValues
    Else
      Range("C10:C22").ClearContents
      Range("A10").Copy
[B]      Range("C10" & last).PasteSpecial xlPasteValues[/B]
    End If
    Application.CutCopyMode = xlCut
    Range("A10").Select
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Call Movedata
End Sub


Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "Data", , False
End Sub

The text in BOLD highlights yellow when i click debug..

any help will be much appreciated.

 

[LRSD781]

Sorry, I forgot to post the error code:

Run-time error '1004'
Method "Range 'of object'_Global" failed

 

[sericom]

Should it be

[B]Range("C" & last).PasteSpecial xlPasteValues[/B]

or

[B]Range("C" & 10 + last).PasteSpecial xlPasteValues[/B]

 

[LRSD781]

Thanks for the reply sericom,

the first code example keeps pasting past C21 and so on...
the second code skips 10 rows and continues to paste..

 

[sericom]

Ok, try this then

[B]Range("C10").PasteSpecial xlPasteValues[/B]

 

[LRSD781]

sericom,
Thanks a bunch, it's been running for a few minutes without issue...

Now all I need is to change my Time to run value to 01:00:00 for one hour.

Can you help me with some code to clear contents of column "C" at 4:30 AM and start the macro at 5:00 AM ?

 

[LRSD781]

I need help editing the code below.

I need it to clear contents of column "C" and re-start the copy/paste function at 4:00 AM every day.

Excel will be running 24/7 (dashboard)

Dim TimeToRun

Sub auto_open()
    Call Movedata
End Sub


Sub Movedata()
    TimeToRun = Now + TimeValue("00:00:10")
    Application.OnTime TimeToRun, "Data"
End Sub


Sub Data()
     Dim last As Long
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    last = Application.WorksheetFunction.Max(10, Cells(Application.Rows.Count, "C").End(xlUp).Row + 1)
    If last <= 21 Then
      Range("A10").Copy
      Range("C" & last).PasteSpecial xlPasteValues
    Else
      Range("C10:C34").ClearContents
      Range("A10").Copy
      Range("C10").PasteSpecial xlPasteValues
    End If
    Application.CutCopyMode = xlCut
    Range("A10").Select
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Call Movedata
End Sub


Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "Data", , False
End Sub

 

[LRSD781]

How do I assign this code on two separate sheets?
In other words, I have two sheets, "HA3" and "HA6".
I want to macro to apply to both sheets.