Htykuut

When the line reaches lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper") I get the error:

When I try to replace it with:
lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")), I get the error:

I believe the name of the table is correct as shown in the image below:

Option Explicit

Dim filterRangeFoilProfile As Range, filteredRangeFoilProfile As Range

Private Sub cbxSupplier_AfterUpdate()

Dim Supplier_col As Long



lbxFoilInfoDisplay.RowSource = vbNullString

Supplier_col = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index

filterRangeFoilProfile.AutoFilter Field:=Supplier_col, Criteria1:=cbxSupplier.Text

On Error Resume Next

Set filteredRangeFoilProfile = Intersect(filterRangeFoilProfile, filterRangeFoilProfile.Offset(1, 0)).SpecialCells(xlCellTypeVisible)

On Error GoTo 0



If Not filteredRangeFoilProfile Is Nothing Then

    ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

    ThisWorkbook.Worksheets("List_Box").Cells(2, 1).PasteSpecial

    lbxFoilInfoDisplay.RowSource = "tblFoilInfoHelper!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")

    'lbxFoilInfoDisplay.List = ThisWorkbook.Worksheets("List_Box").Range("A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper"))

End If



End Sub

User-Defined Functions (to add context):

1.)

Function Col_Letter(lngCol As Long) As String

    Dim vArr

    vArr = Split(Cells(1, lngCol).Address(True, False), "$")

    Col_Letter = vArr(0)

End Function

2.)

Function TotalRowsCount(WBString As String, WorksheetNameString As String, TableNameString As String) As Long



Dim Total_columns As Long

Dim i As Long



Total_columns = Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns.Count



ReDim Rows_count(1 To Total_columns) As Long

For i = 1 To Total_columns

    With Workbooks(WBString).Worksheets(WorksheetNameString).ListObjects(TableNameString).ListColumns(i).Range

    Rows_count(i) = .Find(What:="*", _

        After:=.Cells(1), _

        Lookat:=xlPart, _

        LookIn:=xlFormulas, _

        SearchOrder:=xlByRows, _

        SearchDirection:=xlPrevious, _

        MatchCase:=False).Row

    End With

Next i

TotalRowsCount = WorksheetFunction.Max(Rows_count)



End Function

3.)

Function TotalColumnsCount(WBString As String, WorksheetNameString As String) As Long



Dim lastColumn As Long

lastColumn = Workbooks(WBString).Worksheets(WorksheetNameString).Cells(1, Columns.Count).End(xlToLeft).Column

TotalColumnsCount = lastColumn



End Function

Use the sheet name instead of the table name. ie: "List_Box!A1:" &...

lbxFoilInfoDisplay.RowSource = "List_Box!A1:" & Col_Letter(TotalColumnsCount("Foil Purchases.xlsm", "List_Box")) & TotalRowsCount("Foil Purchases.xlsm", "List_Box", "tblFoilInfoHelper")

Might find some use for this... or not. You can do this using the table as well, but you still need to use the Sheetname in order to get the address of the cells. ie.

ActiveSheet.ListObjects(1).Range.Cells(3, 1).Address

This is useful if you need to refer to specific cells in your table, and care less where the table is located. However this is only to help you build the address part - and not to replace the sheet name with the table name.