Create Table VBA Not Working

[shellp]

Hello

I am trying to create a table via VBA. It works when I am on the worksheet that I want the table to be on, but this worksheet will be hidden so I need it to work by allowing me to identify the worksheet. I cannot figure out why I am getting a run-time error "the worksheet range for the table data must be on the same sheet as the table being created". Below code works when I am on the page of "chart_list" but I not if I am on another worksheet.

Sub make_table()
Dim ws As Worksheet
Dim lrow As Long
Dim lcol As Long
Dim rng As range
 
 
Set ws = ThisWorkbook.Sheets("Chart_List")
 
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
lcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
ws.ListObjects.Add(xlSrcRange, range(Cells(1, 1), Cells(lrow, lcol)), , xlYes).Name = "Chart_List"
 
End Sub

 

[Akuini]

Untested but maybe this:

Sub make_table()
Dim ws As Worksheet
Dim lrow As Long
Dim lcol As Long
Dim rng As Range
 
 
Set ws = ThisWorkbook.Sheets("Chart_List")
With ws
lrow = .Cells(.Rows.count, "A").End(xlUp).Row
lcol = .Cells(1, .Columns.count).End(xlToLeft).Column
.ListObjects.Add(xlSrcRange, .Range(.Cells(1, 1), .Cells(lrow, lcol)), , xlYes).Name = "Chart_List"
End With
End Sub

 

[shellp]

I figured out another way to do this. I already reference number of rows of data for another purpose on another worksheet so I referenced it here and used the range value.

Sub make_table()
Dim ws1 As Worksheet
Dim ws2 as worksheet
Dim r_long As range
 
 
Set ws1 = ThisWorkbook.Sheets("Chart_List")
Set ws2=ThisWorkbook.Sheets("Export")

R_Long=ws2.range("C8:C8")
 
ws.ListObjects.Add(xlSrcRange, ws1.range("A$1:$R$"& R_Long), , xlYes).Name = "Chart_List"
 
 End Sub