Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific # of rows below selected cell - please help
I have about 500 identical forms people fill out, all on a single
worksheet. They occupy Columns A, B C and D. They are arranged vertically. Each form is 73 rows long, with a couple of blank lines between each one. The data we need is in Column D. The staff will identify certain forms. On each identified form, Copy the data in Column D, and do a Paste Special/Transpose onto another worksheet in another workbook. I'm trying to fix a macro that lets them just select the first data cell in Column D on the form, then they execute the macro and it automatically selects and copies the next 68 cells below it. At first, it looks simple: ----------------------- Sub copydata() ' ' copydata Macro ' Macro recorded 7/23/2007 by rmorgan ' ' Keyboard Shortcut: Ctrl+Shift+A ' Range("D5:D73").Select Selection.Copy End Sub ------------------------ I need to be able to select ANY cell in Column D, and then the macro selects and copies that cell and the next 68 cells below it. The problem is, every time the macro executes, it jumps back up to Row 5 and selects/copies Cells D5 through D73. I've done some reading on the Range method, but can't find anything that fixes this. Can somebody please tell me how the code above could be tweaked to do this? Thanks, Please reply to the newsgroup. Ron M. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific # of rows below selected cell - please help
You can use Offset
Range(ActiveCell, ActiveCell.Offset(68, 0)).Copy -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message oups.com... I have about 500 identical forms people fill out, all on a single worksheet. They occupy Columns A, B C and D. They are arranged vertically. Each form is 73 rows long, with a couple of blank lines between each one. The data we need is in Column D. The staff will identify certain forms. On each identified form, Copy the data in Column D, and do a Paste Special/Transpose onto another worksheet in another workbook. I'm trying to fix a macro that lets them just select the first data cell in Column D on the form, then they execute the macro and it automatically selects and copies the next 68 cells below it. At first, it looks simple: ----------------------- Sub copydata() ' ' copydata Macro ' Macro recorded 7/23/2007 by rmorgan ' ' Keyboard Shortcut: Ctrl+Shift+A ' Range("D5:D73").Select Selection.Copy End Sub ------------------------ I need to be able to select ANY cell in Column D, and then the macro selects and copies that cell and the next 68 cells below it. The problem is, every time the macro executes, it jumps back up to Row 5 and selects/copies Cells D5 through D73. I've done some reading on the Range method, but can't find anything that fixes this. Can somebody please tell me how the code above could be tweaked to do this? Thanks, Please reply to the newsgroup. Ron M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In VBA, How to Refer to Cell In Specific Column But Selected Rows? | Excel Discussion (Misc queries) | |||
Macro to delete specific rows above selected cell | Excel Programming | |||
select specific rows | Excel Programming | |||
select and delete specific rows | Excel Discussion (Misc queries) | |||
select rows to print where there's data in specific column | Excel Discussion (Misc queries) |