Unique formulas in table managed by code

Question

I have a workbook with an "Index" sheet/tab that maintains a list of all projects tracked in the workbook. Each project has its own tab. I'm attempting to manage this programmatically with VBA using the following code:

Dim template As Worksheet
Dim newSheet As Worksheet
Dim newName As String


newName = Application.InputBox("Enter Project Name")        'get name
Worksheets("P0").Visible = True                             'Unhide template
Set template = ActiveWorkbook.Sheets("P0")                  'identify template
template.Copy After:=Sheets(Sheets.Count)                   'create copy
Set newSheet = ActiveSheet                                  'identify new sheet
newSheet.Name = newName                                     'rename sheet
'deleteNames (newSheet.Name)                                'delete copied named ranges with worksheet scope
newSheet.Range("C5").Value = newName                        'Change header on new sheet
Worksheets("P0").Visible = False                            'Hide template
Worksheets("Dashboard").Activate                            'switch back to dashboard

Dim Tbl As ListObject
Dim StrFormula1, StrFormula2 As String
Dim NewRow As ListRow
StrFormula1 = "=HYPERLINK(""#'" & newName & "'!A1"",'" & newName & "'!C5)"
StrFormula2 = "='" & newName & "'!C18"

Set Tbl = Range("tblProjects").ListObject
Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)
NewRow.Range = Array(StrFormula1, StrFormula2)

The probelm is the last line. It works great the first time. It creates a new row with a formula like this:

=HYPERLINK("#'Test Project'!A1",'Test Project'!C5)

But when I add a second one, it changes the previous row:

=HYPERLINK("#'Another Project'!A1",'Another Project'!C4)

It should I would like it to still be referencing cell C5. Someone said I could "enter a constant into one cell of the column. Now change the formula in another cell of the column. Change the first cell back to its original formula." but I'm hoping for a cleaner solution. Is there a setting or something to disable this behavior?

I need (would like) it to be a table so I can easily sort and filter and eventually add functionality to delete projects or manipulate the table in other ways.

Answer 1

Why don't you add another column where the sheet name is entered.

Then create a formula that references the column in the hyperlink function. When you add a new row, you only need to add the sheet name in the sheet name column. The Excel Table default behavior is to apply formulas to new rows.

=HYPERLINK(INDIRECT("'"&A2&"'!A1"),INDIRECT("'"&A2&"'!C5"))

The VBA code now just needs to add a value in a new row and populate the "sheet" column with the sheet name and you don't need to edit the formula at all.

Edit: On the other hand: If the behavior of an Excel Table is causing problems, why do you want to use a table at all? If you don't use a table, there won't be errors. If you are setting the formulas for each row with VBA, then just us a regular range, not a table.