How to refer to Table inside a sheet using ListObjects

Hello to all, very new to vba and starting to learn macros.

I have macros that are saved inside each sheet in my workbook via Worksheet_Calculate()

I was able to refer to workbook where the code is save using "ThisWorkbook"
Also to the sheet where the code is save using "Sheets(Me.Name)"

Q1 - How can I refer to the table where the code is save? I have about 90 sheets in my workbook and each sheet has 1 table inside.

Q2 - Once I identified the table, How can I auto resize it using CountifA function I put in cell B1. My range starts with cell A5 and end at cell J(value of cell B1).

Kindly refer to the code below. Thanks.

Private Sub Worksheet_Calculate()

Dim wks As Worksheet

Set wks = ThisWorkbook.Sheets(Me.Name)

wks.ListObjects("Table Name").Resize Range("$A$5:$J(value of cell B1)")

End Sub

if there is only one table per worksheet, then do the following

Please Login or Register  to view this content.

Thanks dmcgov! The code worked for me since I only have one table per worksheet.

Here is what my code looked like...

Private Sub Worksheet_Calculate()

Dim wks As Worksheet

Set wks = ThisWorkbook.Sheets(Me.Name)

wks.ListObjects(1).Resize Range("A5:J" & Range("A1").Value2)

End Sub

Hi Norie, been trying to make the code work but to no avail...can you please write the complete lines of codes.
Thank you.

By the way this code worked for me...

Private Sub Worksheet_Calculate()

Dim wks As Worksheet

Set wks = ThisWorkbook.Sheets(Me.Name)

wks.ListObjects(1).Resize Range("A5:J" & Range("A1").Value2)

End Sub

Tried it...using Me.ListObjects(1)... and just ListObject(1)...
There is an error that says
Run-time error ‘1004’:
Application-defined or object-defined error
...maybe because the code should be running inside the sheet even if it is not the active workbook or sheet

Originally Posted by xlnitwit

What Norie means is this

Please Login or Register  to view this content.

you can in fact omit the "me." part completely

Originally Posted by Norie

Where is the code located?

Also, what's the value in A1?

Code is located in each sheet ...via private sub worksheet_calculate()
A1 is =IF(ISNA(B1),6,B1+6)
B1 is =OFFSET([DailySettings.xlsm]INFO!$A$6,M1,1) ....from another sheet
INFO!$A$6 =COUNTIF(MAIN!E:E,A7)

Originally Posted by Norie

Where in the sheets are the tables located?

Table is located in each sheet, from cell A5 to cell J(value of A1)

maybe you can suggest another solution because putting the dynamic range of the table inside worksheet_calculate make the entire workbook run so slow because any change in the other workbook (DailySettings.xlsm) will make this workbook (DailyLogs.xlms - where the codes per sheet is located) update and it takes to long....have to force quit at times.
I just want to set the range of my table to auto expand based on the count in the other workbook (DailySettings.xlsm) without macro....but excel doesn't seem to allow me to do that . The formula inside the named range for table is grayed out.

Maybe you can just give me a code that i can manually run in the other workbook (DailySettings.xlsm) to update each sheets' table range (each sheet in DailyLogs.xlsm) based on the current count for the day. The reference will be the name of the cell in the other workbook (DailySettings.xlsm) is equal to the name of the sheet in this workbook (DailyLogs.xlsm).
any help/idea will be appreciated. Thanks!