Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating an array | Excel Worksheet Functions | |||
vba function ( populating rows) | Excel Discussion (Misc queries) | |||
Populating several cells | Excel Worksheet Functions | |||
Populating another tab. | Excel Discussion (Misc queries) | |||
populating from sheets -- and PLEASE don't tell me I need VB! | New Users to Excel |