Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 1
Default 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. :)




--
gusdafa
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 11,058
Default Populating Rows Below

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

  #3   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 1
Default Populating Rows Below


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
  #4   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 11,058
Default Populating Rows Below

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

  #5   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 11,058
Default Populating Rows Below

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populating an array Bucs85027 Excel Worksheet Functions 0 February 14th 08 01:32 AM
vba function ( populating rows) Bre-x Excel Discussion (Misc queries) 3 July 13th 07 12:04 AM
Populating several cells A Excel Worksheet Functions 1 September 15th 05 05:58 PM
Populating another tab. Brian Excel Discussion (Misc queries) 1 June 24th 05 12:29 AM
populating from sheets -- and PLEASE don't tell me I need VB! RedHeadedMenace New Users to Excel 4 April 2nd 05 11:12 PM


All times are GMT +1. The time now is 09:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"