VBA to copy data from one cell, based on data in another cell (formula output), and paste at the end of a table in another sheet

[keikii]

I've been trying to figure this out for at least ten hours total now, and I have to admit I cannot do it myself. I'm just too new at VBA and too bad at programming.

Ultimate End Goal: to create a table of Authors based on data entered in a primary table, upon entry or update, without duplicates and ignoring blanks

Current goal: to copy a cell with the Author Name from the primary table to the end of a table on another sheet on entry or update, without duplicates and ignoring blanks

current setup: column with Author Name, validation column with whether or not that Author Name is in the Author table​

current problem: - I really do not have a clue what I'm doing​

- I can get the cell to transfer over to the second sheet if I enter data in a new row with Author Name column first, and all other data after that, but I need to be able to have it update on change, too.​

previous setup: macro to copy cell of appropriate column in selected row to Author Table on double click
previous problem: ultimately needed more automation

working VBA Code:​

Sub AuthorCopyIdent()'
' Macro to Copy Author to Author Table
'
Range("C" & (ActiveCell.Row)).Select
Selection.Copy
Sheets("Authors").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Books").Select
End Sub

Note: Worked well for the previous setup, but I cannot figure out how to alter for more automation
Secondary Note: this does not seem to work if there is one row in the table that is blank. Minor issue, but a bit irksome.

Private Sub Worksheet_Calculate()
     On Error GoTo myerror
    Application.EnableEvents = False
    If Me.Range("J1").Value <> Me.Range("K1").Value Then
        Me.Range("K1").Value = Me.Range("J1").Value
 
        Call Module3.AuthorCopyIdent
    End If




myerror:
    Application.EnableEvents = True
End Sub
Module 1:[INDENT]Public Function LastRow() As Long[/INDENT]
[INDENT]    Application.Volatile[/INDENT]
[INDENT]    ActiveSheet.AutoFilterMode = False[/INDENT]
[INDENT]    LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/INDENT]
[INDENT]End Function[/INDENT]

Notes: Works great if I'm adding a new row, but because it calls the old setup macro, it works only on TAB not ENTER, and I would like it to work for both. Also does not work if I add data in in any other column but the author name column first.
Original Source Code: https://www.mrexcel.com/forum/excel...pon-new-row-insert-using-vba.html#post3909496
​

Also tried: a worksheet_change sub that I have since lost the code for because it doesn't work because my validation column is based on a formula
​

Anticipated problems: if I have to edit the author name for any reason, it would create an entirely new row in the Authors table, when I would want to update the old entry if possible, or delete if it was a misspelled dupe. I haven't even been able to begin working on this anticipated problem

Excel version: current Windows version.

I don't know what else to add to this, if you have any questions just ask.

Any help at all would be helpful, even if I'm just going about this all wrong. Thank you for looking at this because I'm seriously lost.

​