Search code examples
excelvba

"Subscript out of range" error for ListObject

I call a sub that select and convert ranges in multiple sheets into tables. It generates the tables and names them "SIS_1", "SIS_2" and so on. To use these tables in my current sub, I can declare and set table "SIS_1" in worksheets 3, but can't declare and set table "SIS_2" in worksheet 4 and so on. Does it have anything to do with the sheet code (highlighted in red)?

IMG2

I can't seem to find what the issue is. If I manually rename the "SIS_2" in excel into "SIS_x", then rename it back to "SIS_2", it seems to solve the issue and I can then declare the variable as usual. I don't understand why it's like that.

IMG1

Code i want to declare the ListObject

Public Sub ImportButton_Click()

    Dim totalSheets As Integer
    Dim startSheet As Integer, endSheet As Integer
    Dim masterSIS As ListObject, sourceSIS As ListObject
    Dim i As Integer

    Set masterSIS = Worksheets("MasterSIS").ListObjects("MasterSIS")

    totalSheets = ThisWorkbook.Worksheets.Count

    Call importSheets([filePath])

    startSheet = totalSheets + 1
    endSheet = ThisWorkbook.Worksheets.Count

    Call convertToTable(startSheet, endSheet, "SIS")

    For i = 1 To endSheet - totalSheets
        Set sourceSIS = Worksheets(startSheet).ListObjects("SIS_" & i)
        Call appendTable(masterSIS, sourceSIS)
        startSheet = startSheet + 1
    Next i

End Sub

For anyone having similar problem. Hope this helps.

Found the solution here https://www.dingbatdata.com/2017/11/24/referencing-listobjects-with-vba/.

To declare LinkObject, you need to do it like this Range("TableName").ListObject.It seems that 'there will be a VBA error if the sheet changes it’s position'.

Solution

  • Found the problem with the table name wrongly generated from the loop code in previous sub. Fixed it and the error disappear.