Populating Rows Below

Hi there, I hope this is the right section for this.

Got excellent answers on my first query here (thanks again)and it gave me an idea of how to further develop my spreadsheet.

Basically, I have a drop down list of names where once a name is selected, the time and date is populated. What I want to do now is to "push" this entry down the next row while freeing up the original top row for a new selection and updates. I would like to do this so that entries are in a descending order with the selection row left blank up top.

I've searched google for a remedy and came up with nothing.

Thanks for your time and effort in helping me.

This assumes that the name is in A1 and the date/time are in B1. Run this
macro:

Sub push_down()
Set r1 = Range("A1:B1")
Set r2 = Range("A2:B2")
r1.Copy r2
r1.ClearContents
End Sub

The macro clears A1:B1, but leaves the pull-down in place for the next entry.
--
Gary''s Student - gsnu2007d


"gusdafa" wrote:


Hi there, I hope this is the right section for this.

Got excellent answers on my first query here (thanks again)and it gave
me an idea of how to further develop my spreadsheet.

Basically, I have a drop down list of names where once a name is
selected, the time and date is populated. What I want to do now is to
"push" this entry down the next row while freeing up the original top
row for a new selection and updates. I would like to do this so that
entries are in a descending order with the selection row left blank up
top.

I've searched google for a remedy and came up with nothing.

Thanks for your time and effort in helping me.




--
gusdafa

Originally Posted by Gary''s Student

This assumes that the name is in A1 and the date/time are in B1. Run this
macro:

Sub push_down()
Set r1 = Range("A1:B1")
Set r2 = Range("A2:B2")
r1.Copy r2
r1.ClearContents
End Sub

The macro clears A1:B1, but leaves the pull-down in place for the next entry.
--
Gary''s Student - gsnu2007d

Thanks for that, it worked.

Can you give some pointers with storing the previous copy on the rows below it (A3:B3) instead of it being overwritten? That plus the macro to autorun with each selection (at the moment I have to manually run the macro after a selection), and finally, a better handle on ClearContents as it not only clears out the cell content but also the formulas? I'm sorry if it sounds like I'm asking for the moon. I'll be doing some research on those queries but appreciate any help.

Thanks.

This is more automatic. It is a worksheet event macro. It waits for you to
change cell A1. Once you change cell A1, a copy of the entire first row is
"pushed down". You can now update the values in the first row and when you
update A1, the new row will also be "pushed down"


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm




--
Gary''s Student - gsnu2007d


"gusdafa" wrote:


Gary''s Student;2644827 Wrote:
This assumes that the name is in A1 and the date/time are in B1. Run
this
macro:

Sub push_down()
Set r1 = Range("A1:B1")
Set r2 = Range("A2:B2")
r1.Copy r2
r1.ClearContents
End Sub

The macro clears A1:B1, but leaves the pull-down in place for the next
entry.
--
Gary''s Student - gsnu2007d

Thanks for that, it worked.

Can you give some pointers with storing the previous copy on the rows
below it (A3:B3) instead of it being overwritten? That plus the macro
to autorun with each selection (at the moment I have to manually run
the macro after a selection), and finally, a better handle on
ClearContents as it not only clears out the cell content but also the
formulas? I'm sorry if it sounds like I'm asking for the moon. I'll be
doing some research on those queries but appreciate any help.

Thanks.




--
gusdafa

Here is the event code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Rows("2:2").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Application.EnableEvents = True
End Sub
--
Gary''s Student - gsnu2007d


"gusdafa" wrote:


Gary''s Student;2644827 Wrote:
This assumes that the name is in A1 and the date/time are in B1. Run
this
macro:

Sub push_down()
Set r1 = Range("A1:B1")
Set r2 = Range("A2:B2")
r1.Copy r2
r1.ClearContents
End Sub

The macro clears A1:B1, but leaves the pull-down in place for the next
entry.
--
Gary''s Student - gsnu2007d

Thanks for that, it worked.

Can you give some pointers with storing the previous copy on the rows
below it (A3:B3) instead of it being overwritten? That plus the macro
to autorun with each selection (at the moment I have to manually run
the macro after a selection), and finally, a better handle on
ClearContents as it not only clears out the cell content but also the
formulas? I'm sorry if it sounds like I'm asking for the moon. I'll be
doing some research on those queries but appreciate any help.

Thanks.




--
gusdafa