CODEWITHSUNDEEP
vba
senthil ramamurthy
senthil ramamurthy

Reputation: 53

Unable to select worksheet by name

I'm trying to assign the first sheet of the current workbook to sheet object, but I get a Runtime error "91", Object variable or with block variable not set. 

Sub Test
Dim InSheet as Worksheet
InSheet = ThisWorkbook.Sheets("Sheet1")
End Sub

However I'm able to loop through the worksheet collection like so

For Each ws in ThisWorkbook.Sheets
Debug.Print ws.Name
Next

Sheets("Sheet1") or Sheets(1) do not work. What am I doing wrong?

Any help would be greatly appreciated. Thank you

Upvotes: 1

Views: 325

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9471

Assuming the workbook has a sheet named "Sheet1", you just need to use Set, and better still, if you know it's a worksheet, and not a chart sheet, use Worksheets:

Sub Test
Dim InSheet as Worksheet
Set InSheet = ThisWorkbook.Worksheets("Sheet1")
End Sub

But you might find it easier to give the sheet a CodeName like InSheet in the properties window, and then you can just refer to it in code, regardless of whether somebody changes its name, like so:

Sub Test
  Debug.Print InSheet.Name
End Sub

Upvotes: 2

Related Questions