I would like to enter values in a single cell and have it update each time
the next consecutive empty cell in a cell range. For example, cell c5 is the
data entry cell:

Each time I enter a value the next empty cell in range a10 through a20 is
updated. And the previous entered is frozen to values. I am thinking I have
to include a "edit-copy-edit-paste special- values" macro to freeze every
entry after it places.

Every time I enter a value in c5, the below range update. Your input on how
to do this will be appreciated.

A
10 55
11 84
12 73
13 12
14 22
15
16
17
18
19
20

Copy this code to the macro area of the specific worksheet that you are
working on and any value entered in cell C5 of that worksheet will be copied
to the bottom of the column that starts with cell A10.

Private Sub worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C5")) Is Nothing Then
rwRow = Range("A10").CurrentRegion.Rows _
(UBound(Range("A10").CurrentRegion.Value, 1)).Row
Range("A" & rwRow + 1).Value = Range("C5").Value
End If

End Sub

Let me know if you have further questions.

Tom

Thanks Tom, I will update you, and thanks for being open to followup
questions - Sony

"tompl" wrote:

Copy this code to the macro area of the specific worksheet that you are
working on and any value entered in cell C5 of that worksheet will be copied
to the bottom of the column that starts with cell A10.

Private Sub worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C5")) Is Nothing Then
rwRow = Range("A10").CurrentRegion.Rows _
(UBound(Range("A10").CurrentRegion.Value, 1)).Row
Range("A" & rwRow + 1).Value = Range("C5").Value
End If

End Sub

Let me know if you have further questions.

Tom

Tom, Where in the worksheet do I put this code? And get it there. A
separate tab? When I do this does it mean the entry will advance to the next
empty cell? Thanks - Sony

"tompl" wrote:

Copy this code to the macro area of the specific worksheet that you are
working on and any value entered in cell C5 of that worksheet will be copied
to the bottom of the column that starts with cell A10.

Private Sub worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C5")) Is Nothing Then
rwRow = Range("A10").CurrentRegion.Rows _
(UBound(Range("A10").CurrentRegion.Value, 1)).Row
Range("A" & rwRow + 1).Value = Range("C5").Value
End If

End Sub

Let me know if you have further questions.

Tom

This is a Macro and if you have not worked with them before it might be a
little advanced for you. I am sorry if all this just adds confusion.

To install this macro you need to open the VB Editor aka Macro Editor.
First, open your workbook, then open the VB Editor by pressing Alt F11. With
that open you should be able to see a list of worksheets in you workbook. If
not press Ctrl R to open the Project Explorer. Next, double click on the
worksheet name and the €œCode€� window will open and focused on that specific
worksheet. Paste the code below into the €œCode€� window and that should do it.

I re-thought my earlier code and changed it a little so use this code
instead of the previous. When installed, each time you enter something in
Cell C5 of that particular worksheet, the same value will be automatically
added to the next empty cell below cell A10. Good luck, Tom

Private Sub worksheet_Change(ByVal Target As Range)

Dim rwRow as long

If Not Intersect(Target, Range("C5")) Is Nothing Then
rwRow = Range("A10").End(xlDown).Row
Range("A" & rwRow + 1).Value = Range("C5").Value
End If

End Sub

Tom, Thank-you. One functionality is missing, but maybe it's how I entered.
Each time I enter a value in "C5" the new entry populates "A10". The next
entry does not update cell "A11", but replaces the previously entered value
in "A10". I copied and pasted just as directed below. Any thoughts.

Your insight is much appreciated - Tom

"tompl" wrote:

This is a Macro and if you have not worked with them before it might be a
little advanced for you. I am sorry if all this just adds confusion.

To install this macro you need to open the VB Editor aka Macro Editor.
First, open your workbook, then open the VB Editor by pressing Alt F11. With
that open you should be able to see a list of worksheets in you workbook. If
not press Ctrl R to open the Project Explorer. Next, double click on the
worksheet name and the €œCode€� window will open and focused on that specific
worksheet. Paste the code below into the €œCode€� window and that should do it.

I re-thought my earlier code and changed it a little so use this code
instead of the previous. When installed, each time you enter something in
Cell C5 of that particular worksheet, the same value will be automatically
added to the next empty cell below cell A10. Good luck, Tom

Private Sub worksheet_Change(ByVal Target As Range)

Dim rwRow as long

If Not Intersect(Target, Range("C5")) Is Nothing Then
rwRow = Range("A10").End(xlDown).Row
Range("A" & rwRow + 1).Value = Range("C5").Value
End If

End Sub

tom, One more question. Within the worksheet I have other data entry cells
with references to specific cell ranges. I want to set up for the same type
of entry.

In VBA I copied the code in and edited the references (enter in K29, update
next consecutive empty cell in range c243:C254.

The original code is in Template, the copied and edited code in Template2.
I can't get Template2 to run though. Both, when you open the module are
named "worksheet".

What do I have to adjust to be able to enter updates from both modules?

Thank-you.

"tompl" wrote:

This is a Macro and if you have not worked with them before it might be a
little advanced for you. I am sorry if all this just adds confusion.

To install this macro you need to open the VB Editor aka Macro Editor.
First, open your workbook, then open the VB Editor by pressing Alt F11. With
that open you should be able to see a list of worksheets in you workbook. If
not press Ctrl R to open the Project Explorer. Next, double click on the
worksheet name and the €œCode€� window will open and focused on that specific
worksheet. Paste the code below into the €œCode€� window and that should do it.

I re-thought my earlier code and changed it a little so use this code
instead of the previous. When installed, each time you enter something in
Cell C5 of that particular worksheet, the same value will be automatically
added to the next empty cell below cell A10. Good luck, Tom

Private Sub worksheet_Change(ByVal Target As Range)

Dim rwRow as long

If Not Intersect(Target, Range("C5")) Is Nothing Then
rwRow = Range("A10").End(xlDown).Row
Range("A" & rwRow + 1).Value = Range("C5").Value
End If

End Sub