Adding row in a table when the name is not defined

[WxShady13]

Hello. I am using a Macro to create a worksheet that contains a table. The name of the table is PayData, however since this table is on every worksheet (individual employees) Excel gives it a name such as PayData28. How can I use the below VBA code to identify the PayData table that is on the worksheet properly?

Sub AddRow()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("A9")
'add a row at the end of the table
tbl.ListRows.Add
'add a row as the fifth row of the table (counts the headers as a row)
End Sub

 

[Fluff]

If you only have one table on each sheet, you could use

Set tbl = ws.ListObjects(1)

 

[WxShady13]

If you only have one table on each sheet, you could use

Set tbl = ws.ListObjects(1)

The problem is I have 4 tables on each worksheet.

 

[Fluff]

Does the pay table always start in the same cell on every sheet?

 

[WxShady13]

Does the pay table always start in the same cell on every sheet?

Yes the header row is always A9

 

[Fluff]

In that case you can use

Set tbl = Range("A9").ListObject

 

[WxShady13]

In that case you can use

Set tbl = Range("A9").ListObject

You are awesome! Thank you

 

[Fluff]

You're welcome & thanks for the feedback.