0

Im getting a Run-time error '1004':

Select method of range class failed

This only happens when I move away from the sheet (Bet Angel) that has the macro running to another sheet. The code is below:

    Sub DeleteStatus()

Worksheets("Bet Angel").Range("O6:O50").Select
Selection.ClearContents
Call Start
End Sub

Sub Start()
    Application.OnTime Now + TimeValue("00:00:15"), "DeleteStatus"
End Sub

I want the macro to run even if I am moving between different sheets in the workbook.

braX
  • 11,506
  • 5
  • 20
  • 33
Zipping2010
  • 15
  • 5
  • [Why are you `Select`inig to begin with?](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) `Worksheets("Bet Angel").Range("O6:O50").ClearContents`. – BigBen Jul 02 '21 at 01:39

1 Answers1

0

Since you are moving to another worksheets, you need to activate or select the target worksheet first.

Sub DeleteStatus()

Sheets("Bet Angel").Activate
Worksheets("Bet Angel").Range("O6:O50").Select
Selection.ClearContents
Call Start
End Sub

Sub Start()
    Application.OnTime Now + TimeValue("00:00:15"), "DeleteStatus"
End Sub

If you want to go back to your original worksheet, you can activate it at the end of DeleteStatus()

Like this

Sub DeleteStatus()

Sheets("Bet Angel").Activate
Worksheets("Bet Angel").Range("O6:O50").Select
Selection.ClearContents
Call Start
Sheets("your original worksheet").Activate
End Sub

If you don't want to use activate, then you need to dim the worksheet first.

Like this

Sub DeleteStatus()
Dim ws As Worksheet

Set ws = Sheets("Bet Angel")
ws.Range("O6:O50").ClearContents
Call Start
End Sub

Sub Start()
    Application.OnTime Now + TimeValue("00:00:15"), "DeleteStatus"
End Sub
GGG
  • 486
  • 4
  • 9