Excel macro paste row for certain num of rows

Closed
mazaman Posts 2 Registration date Monday September 14, 2009 Status Member Last seen September 17, 2009 - Sep 15, 2009 at 10:23 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 18, 2009 at 11:26 PM
Hello,

I have a row with data that I reach from a blank row below and perform a count of the number of rows elapsed to get to the row with data. After recording this manually (using a row count macro ofcourse), this is the code that got generated so far:

Range(Selection, Selection.End(xlUp)).Select
Application.Run "Results_BW.xls!CountRow"

Where the code for CountRow is:

Sub CountRow()
myCountRow = Selection.Rows.Count

All I would like to know is how to get Excel to copy a row a certain number of rows below it, where the number is the row count I just performed.

If there is any need for further clarification then please let me know! Any help is appreciated.
Related:

2 responses

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 16, 2009 at 10:54 PM
Hello

Just try
Rows(selection.row).Copy
Rows(Selection.Rows.Count ).Select
ActiveSheet.Paste
mazaman Posts 2 Registration date Monday September 14, 2009 Status Member Last seen September 17, 2009
Sep 17, 2009 at 10:17 AM
Thanks.

There is 1 more restriction however. The row copied is within the first 8 columns, and not beyond as those columns contain other data.

The code Excelguru suggested...
Rows(selection.row).Copy
Rows(Selection.Rows.Count ).Select
ActiveSheet.Paste


..seems to work for the entire row, but I only want to copy the first 8 column of the row, and extend the first 8 column into the rows below (number of rows determined by count).
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 18, 2009 at 11:26 PM
Hello,

Try this

range(cells(selection.row,1),cells(selection.row+count,8)).copy