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!!!!