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.

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.