Run-time error '9': SUbscript out of range?

[jassyphee]

This error message pops up when I run the macro, "Run-time error '9': Subscript out of range" I was wondering what could be wrong with the code? "Sheets(sheetName).Activate" is highlighted. THANKS!!!


Here is my code:

Function calAutoNumber(sheetName As String, StartCol As String, startrow As Integer, refName As String)    'Worksheets("Cap. Rec.").Select
    'Worksheets("Cap. Disb.").Select True
    'Worksheets("Rev. Rec.").Select True
    'Worksheets("Rev. Disb.").Select True
    
[B]    Sheets(sheetName).Activate[/B]
    
    Dim bottomrow As Long
   
    bottomrow = Cells(Rows.Count, "B").End(xlUp).row
    
    Range("A5").Formula = "=A4+1"
    Range("'Cap. Disb.'!A4").Formula = "=INDEX('Cap. Rec.'!A5:A1048576, COUNTA('Cap. Rec.'!A5:A1048576), 1)"
    Range("'Rev. Rec.'!A4").Formula = "=INDEX('Cap. Disb.'!A5:A1048576, COUNTA('Cap. Disb.'!A5:A1048576), 1)"
    Range("'Rev. Disb.'!A4").Formula = "=INDEX('Rev. Rec.'!A5:A1048576, COUNTA('Rev. Rec.'!A5:A1048576), 1)"
    
    Application.ScreenUpdating = False
    Range("A5").AutoFill Destination:=Range("A5:A" & bottomrow), Type:=xlFillDefault
    Application.ScreenUpdating = True
   
End Function


Sub getTotals()
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        Dim n As String
        n = Split(nm, "!")(0)
        
        Range(nm).Clear
        nm.Delete
    
    Next nm
    
     calAutoNumber "Cap. Rec.", "A", 5, "Item_No_CR"
     calAutoNumber "Cap. Disb.", "A", 5, "Item_No_CD"
     calAutoNumber "Rev. Rec.", "A", 5, "Item_No_RR"
     calAutoNumber "Rev. Disb,", "A", 5, "Item_No_RD"


End Sub

 

[Jaafar Tribak]

That means the sheetName variable contains an invalid sheet name ...Make sure you are passing the correct sheet name to the calAutoNumber function.(check the sheet name spelling)

Also this can happen if the sheet name you are passing to the calAutoNumber function is in a different workbook from the one the code is located in - In this case try fully qualifying the worksheet as follows :
Workbooks("Workbook_Name_Goes_Here"). Sheets(sheetName).Activate