VBA to record value of a named range cell

[tkaplan]

Hello,
I am a bit rusty in my VBA and can't figure out what I'm doing wrong for what I think is a simple task.

Sheet1 has a named range "EEName"
Sheet2 is a listing of all the EEName that I want to record

I need a macro that will record the value of what the user enters into the EEName named range in Sheet1 in column A of sheet 2 with each time the macro is run, the new record should be in the new blank row.

meaning user enters "Sarah" and then runs the macro. Sheet2 cellA2 should record Sarah
User then enters "John" and runs the macro. macro should find the next blank row in Sheet2 - which would be row 3 now (essentially counting the rows with a value in column A and adding 1) - and record John in cell A3

I feel like this should be really simple, but it's been a few years since I've written VBA (or any coding) and I'm losing it.

Assistance would be appreciated

[Paul_Hossler]

Try this, at least as a starting point

In Sheet1 code module

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells(1, 1).Address = [EEName].Address Then Exit Sub
    
    sOldEEname = [EEName].Value
'    Debug.Print Target.Cells(1, 1).Address & "---" & "Old Name = " & sOldEEname
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells(1, 1).Address <> [EEName].Address Then Exit Sub
    If Target.Cells(1, 1).Value = sOldEEname Then Exit Sub
    
    Application.EnableEvents = False
    With Worksheets("Sheet2")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Target.Cells(1, 1).Value
    End With
    Application.EnableEvents = True
    
End Sub

In standard module

Option Explicit


Public sOldEEname As String

[tkaplan]

thank you so much!!!!