Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copying cell contents to a list
First of all - apologies if this is in the wrong group. I wasn't sure if it should be newusers (which I am) or programming (which I might need). I have inherited a lottery syndicate at work after the previous organiser retired. He kept an Excel 97 spreadsheet with 8 columns - A (Date), B to G (Winning numbers) and H (Bonus number). This list has now grown to about 800 rows as each draw's numbers are added to the bottom of the list (I will be removing about 600). I have adapted the spreadsheet to automatically check our numbers against the winning numbers (using HLOOKUP) after I have keyed them into the top line of the list. My problem is that I want to automatically copy this top line (cells A2:H2) to the the next blank row at the bottom of the list (A801:H801) bearing in mind that next time it will be A802:H802 etc. I eventually managed to record a macro where I can get to the bottom line using "Selection.End(xlDown).Select" (thanks to somebody in this group for that!) but, even after reading TFM and on-line help, I can't get any further. Can anybody please tell me how to drop to the next row and then paste the information? Thanks, Peter |
#2
|
|||
|
|||
This is not an exact solution, but you can adapt it:
Sub add_one_on() Rows("1:1").Select Selection.Copy Rows.End(xlDown).Offset(1, 0).Select ActiveSheet.Paste End Sub This will take the first row, copy it, go to the row below the last row and paste it. Upon repeated calls, it will continue to add rows to the bottom of the sheet. -- Gary''s Student "Peter Horrocks" wrote: First of all - apologies if this is in the wrong group. I wasn't sure if it should be newusers (which I am) or programming (which I might need). I have inherited a lottery syndicate at work after the previous organiser retired. He kept an Excel 97 spreadsheet with 8 columns - A (Date), B to G (Winning numbers) and H (Bonus number). This list has now grown to about 800 rows as each draw's numbers are added to the bottom of the list (I will be removing about 600). I have adapted the spreadsheet to automatically check our numbers against the winning numbers (using HLOOKUP) after I have keyed them into the top line of the list. My problem is that I want to automatically copy this top line (cells A2:H2) to the the next blank row at the bottom of the list (A801:H801) bearing in mind that next time it will be A802:H802 etc. I eventually managed to record a macro where I can get to the bottom line using "Selection.End(xlDown).Select" (thanks to somebody in this group for that!) but, even after reading TFM and on-line help, I can't get any further. Can anybody please tell me how to drop to the next row and then paste the information? Thanks, Peter |
#3
|
|||
|
|||
Peter, give this a try,
Range("A2:H2").Cut Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Peter Horrocks" wrote in message ... First of all - apologies if this is in the wrong group. I wasn't sure if it should be newusers (which I am) or programming (which I might need). I have inherited a lottery syndicate at work after the previous organiser retired. He kept an Excel 97 spreadsheet with 8 columns - A (Date), B to G (Winning numbers) and H (Bonus number). This list has now grown to about 800 rows as each draw's numbers are added to the bottom of the list (I will be removing about 600). I have adapted the spreadsheet to automatically check our numbers against the winning numbers (using HLOOKUP) after I have keyed them into the top line of the list. My problem is that I want to automatically copy this top line (cells A2:H2) to the the next blank row at the bottom of the list (A801:H801) bearing in mind that next time it will be A802:H802 etc. I eventually managed to record a macro where I can get to the bottom line using "Selection.End(xlDown).Select" (thanks to somebody in this group for that!) but, even after reading TFM and on-line help, I can't get any further. Can anybody please tell me how to drop to the next row and then paste the information? Thanks, Peter |
#4
|
|||
|
|||
Thanks guys - works a treat.
Peter "Peter Horrocks" wrote in message ... First of all - apologies if this is in the wrong group. I wasn't sure if it should be newusers (which I am) or programming (which I might need). I have inherited a lottery syndicate at work after the previous organiser retired. He kept an Excel 97 spreadsheet with 8 columns - A (Date), B to G (Winning numbers) and H (Bonus number). This list has now grown to about 800 rows as each draw's numbers are added to the bottom of the list (I will be removing about 600). I have adapted the spreadsheet to automatically check our numbers against the winning numbers (using HLOOKUP) after I have keyed them into the top line of the list. My problem is that I want to automatically copy this top line (cells A2:H2) to the the next blank row at the bottom of the list (A801:H801) bearing in mind that next time it will be A802:H802 etc. I eventually managed to record a macro where I can get to the bottom line using "Selection.End(xlDown).Select" (thanks to somebody in this group for that!) but, even after reading TFM and on-line help, I can't get any further. Can anybody please tell me how to drop to the next row and then paste the information? Thanks, Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ooh .. Linking a list to a list to an output cell | Excel Discussion (Misc queries) | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
cell contents | Excel Discussion (Misc queries) | |||
Can I use the contents of a cell to satisfy the result_vector arg. | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |