Copying a dynamic row

[lpsmith]

Hi,

I would like to find out how to create a macro that will copy a set number of cells in a column no matter where the starting point is. For example I can copy 98 cells in column B starting at B2 or maybe starting at B34 or if I make G3 the active cell then when I run the Macro it will copy the next 98 cells below which ever cell I select.)

Thanks in advance for your help

L.Smith

 

[fumei]

Have you tried recording a macro, doing that?

Gerry

 

[lpsmith]

Yes but once I run the macro it hightlights the cells that I used when I recoreded the macro and not the active cell I'm in at the time.

 

[waynerenaud]

Try this:

Sub Macro1()
Dim StartRow, EndRow, SelectionArea
StartRow = ActiveCell.Address
EndRow = ActiveCell.Offset(98, 0).Address
SelectionArea = StartRow & ":" & EndRow
Range(SelectionArea).Select
End Sub

 

[lpsmith]

That did it

Thanks so much for the quick response