Subscript out of Range Error

[BuffaloGuy]

My code was working perfectly, then I messed with it (only a little!). I was originally copying cells C3:G3, and copying them to the new worksheet. I extended it to include a few more cells that are running an exponetial moving average calculation, and the code runs for 5 or 6 lines before I get the Subscript out of range error. Thoughts?

Option Explicit

Dim TimeToRun

Sub MacroRun()

    TimeToRun = Now + TimeValue("00:01:00")
    Application.OnTime TimeToRun, "Macro1"
    
    
End Sub

Sub Macro1()
    Calculate
    
    Worksheets("Calculation").Range("C3:M3").Copy
    
    Worksheets("Data").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False

MacroRun

End Sub


Sub stopMacros()

    On Error Resume Next
    Application.OnTime TimeToRun, "Macro1", , False
    
End Sub

 

[RoryA]

That error means that the active workbook doesn't have a sheet called Calculations.

 

[BuffaloGuy]

That's not it, the worksheet is spelled correctly. The code runs for a bit, then cuts out.

 

[RoryA]

The only reason I can see for that error would be that you have activated a different workbook, and that one doesn't have a Calculations sheet in it. If you mean the sheet in the same workbook as the code, you should refer to it as Thisworkbook.Worksheets("Calculations")

 

[BuffaloGuy]

If I have multiple workbooks open at the same time, because I want this code running in the background collecting data, could the code be attempting to find a tab called "Calculation" in the active workbook I am working on and not the one I want it running in?

 

[RoryA]

Yes, that's what I'm saying. If you use Thisworkbook.Sheets("Calculations") it will always refer to a sheet in the workbook with the code, regardless of which workbook is active.

 

[BuffaloGuy]

Yes! Thank you so much!