VBA add row to table but can't use table name

[dsrt16]

I have a table called MileageChart on a sheet called Template where when a user presses add row, a new row is added. The VBA code works like a charm. However, there is one problem.

At the start of every year, the template sheet is duplicated and then user is prompted to rename it the name of the new year.

So when the sheet gets duplicated, the table on the new sheet is now called MileageChart1, so then the new row button won't work because there is no ListObject MileageChart on that sheet.

This is the only table on the worksheet; the rest is ranges. So is there a way to get the VBA code to just add a row to the table on the sheet from where the user presses the button regardless of the name of the table?

The code I am currently using which is name specific:

Sub AddRow1()

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("MileageChart")


tbl.ListRows.Add (1)

tbl.ListRows(2).Range.Copy
tbl.ListRows(1).Range.PasteSpecial xlPasteFormats

tbl.ListRows(1).Range.Select

Call Formulalock

End Sub

 

[tonyyy]

dsrt16,

Try changing this line...

Set tbl = ws.ListObjects("MileageChart")

to...

Set tbl = ws.ListObjects(1)

Cheers,

tonyyy

 

[dsrt16]

Oh wow, so easy. I have only worked with named tables in VBA coding.

Thanks!

 

[My Aswer Is This]

I was about ready to answer this when Tony had the answer.

Just curious what your overall object is.
Not sure why you think you need to add rows like this.

And what does this script do:

Call Formulalock
​

 

[tonyyy]

Oh wow, so easy. I have only worked with named tables in VBA coding.

Thanks!

You're very welcome...