Trying to add Formula to Next Empty Cell, but no dice

[Scornakira]

I've been at this for a few days trying to get the correct code in place, and I know it's just there, but everything keeps coming up short.

I'm not quite sure how to implement the " & Rows.Count).End(xlUp).Offset(1) " correctly so that I start at the last cell in my columns, to implement code, and the few time's I've gotten it to work in that cell, it still tries to reference cell 1 of the column instead of current cell.

I don't really want it to add the code all the way down to cell 5000, I'd rather it just enter that data once at the desired cell. The current setup almost does what I want. It adds the formula from E1/F1 down to E5000/F5000, and referencing A1 to A5000 in the formula. I just want to input into the next available cell, and the formula reference the aligned A cell.

Sub updatedata()
        'Start of Macro
    Application.ScreenUpdating = False
    Sheets("Site Roster").Visible = True
    
        'Fill in Shift and Dept of Transcript
    Sheets("Transcript").Select
    With ActiveSheet
    Range("E1:F1").Select
    Range("$E1:$E5000").Formula = "=IFERROR(LEFT(INDEX('Site Roster'!$K:$K,MATCH($A1,'Site Roster'!$B:$B,0)),2),""-"")"
    Range("$F1:$F5000").Formula = "=IFERROR(INDEX(Tracker!$B:$B,MATCH($A1,Tracker!$A:$A,0)),""-"")"
End With

        'Refresh all pivot tables
    Sheets("Landing").Select
    With ActiveSheet
    Dim pc As PivotCache
    For Each pc In ThisWorkbook.PivotCaches
    pc.Refresh
    Next pc
 
        'End of Macro
    Application.ScreenUpdating = True
    Sheets("Site Roster").Visible = False
    Sheets("Landing").Select
 
End With
 
End Sub

 

[Scornakira]

Also, unsure if this affects the formula, but the targeted area is a Table, instead of just the sheet. The table is a refrence point for a Pivot table, and Pivot Chart w/splicers that updates when data gets input in the table.

 

[Scornakira]

So I was able to resolve my issue, but not via VBA macro. I hadn't yet worked with tables yet, but in my research I discovered that Tables remember formulas as new rows are added, and with that I was able to make my sheet work as intended. Then in my refresh tables macro, I also added for it to copy my table, and then paste everything as a value. This reduced data from having too many formulas, but the table still remembers the formula and continues to work after adding new entries.