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:
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:
Code:
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