Run time error 1004: method "select of object_worksheet failed

Question

After uploading the data in Excel file and running the macro code in it to publish the report, getting the

Runtime error 1004 - Method "Select of Object"_Worksheet" Failed

        MailBCC = ""
        MailSubject = "Stack Ranking Result : " & Sheet3.Cells(EmployeeDetailRowNo, 2) & "(" & Sheet3.Cells(EmployeeDetailRowNo, 1) & ")"
        Sheet4.Select
        Sheet4.Range("A1").Select
        Sheet4.Range("C4").Value = Sheet2.Cells(StackRowNo, 1)
        Call SendingMail(MailTo, MailCC, MailBCC, Sheet4.Range("A1:I33"), MailSubject)

        StackRowNo = StackRowNo + 1
    Loop

    Sheet4.Select
    Sheet4.Range("A1").Select
End Sub

No need for all of those instances of `.Select`. This is a front-end feature and is rarely needed on the back-end — urdearboy, Apr 23 '19 at 13:54
[How to avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Samuel Hulla, Apr 23 '19 at 13:59
What line produces the error? Do you have worksheets with **CodeName**s `sheet3` and `sheet4`? — Ron Rosenfeld, Apr 23 '19 at 14:02
Sheet4.Select --- this line gets highlighted when we go in VB debugger — shoaib khan, Apr 23 '19 at 18:06
And do you have a worksheet with the **code name** (**not** the sheet name on the tab) `sheet 4`? — Ron Rosenfeld, Apr 24 '19 at 02:57

score 0 · Answer 1 · answered Apr 23 '19 at 14:01

Take out all those Selects and it should work:

  MailBCC = ""
  MailSubject = "Stack Ranking Result : " & Sheet3.Cells(EmployeeDetailRowNo, 2) & _
  "(" & Sheet3.Cells(EmployeeDetailRowNo, 1) & ")"

  Sheet4.Range("C4").Value = Sheet2.Cells(StackRowNo, 1)
  Call SendingMail(MailTo, MailCC, MailBCC, Sheet4.Range("A1:I33"), MailSubject)

  StackRowNo = StackRowNo + 1
Loop

End Sub

I'll leave this here. You should definitely read it before doing further coding.

Run time error 1004: method "select of object_worksheet failed

1 Answers1