run time error "subscript out of range"

Question

I have written the following script and I'm getting runtime error subscript out of range. Can anyone help me with this?(The msgbox is used as a debugger)
Thanks in advance.

 Sub newbook()
     Dim bk As Workbook, sht As Worksheet
     MsgBox ("variables created")

     Set bk = Workbooks.Add
     MsgBox ("book is set")

     With bk
      .Title = "NewBook"
      .SaveAs Filename:="NewBook.xls"
     End With

    MsgBox ("Book is created")

    Set sht = bk.Sheets.Add
    MsgBox ("sheet is set")

    bk.Sheets(1).Name = "Hello 1"
    bk.Sheets(2).Name = "Hello 2"
    bk.Sheets(3).Name = "Hello 3"
    bk.Sheets(4).Name = "Hello 4"

    MsgBox ("everything is completed")

 End Sub

your code assumes that a new workbook has three sheets by default-that ain't always true ;-) — JosieP

Ekkehard.Horner Ekkehard.Horner · Accepted Answer · 2013-07-16T10:52:06

Use

bk.Sheets.Count

to get the number/last index of sheets, before you try to index into the collection.

run time error "subscript out of range"

1 Answers