How to copy last 10 active rows instead of last active row

Question

Currently i have a vba code that will copy the last active row with formulas and all and paste it in the next row under it. Instead i would like it to copy the last 10 active rows and paste them under it. Can someone help me tweak my current code to help allow this?

    'Sub RunMost()

       Dim ws As Worksheet


           For Each ws In Sheets
            If ws.Name = ("Sheet1") Then
              ws.Activate

    ' Go to last cell
    Cells(Rows.Count, 2).End(xlUp).Offset(2, 0).Select

    ' Copy formula from cell above
    Rows(Selection.Row - 1).Copy
    Rows(Selection.Row).Insert Shift:=xlDown
  End If
  Next ws

End Sub

Have you tried replacing the `Rows(Selection.Row - 1)` with `Rows(Selection.Row - 10)`? — Dave, Jun 13 '16 at 13:56
I recommend reading through [how to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) which should shed some light on this. I believe if you read through this, you can at least *start* tweaking the above to loop. And also @Dave's suggestion... — BruceWayne, Jun 13 '16 at 13:56
yes @Dave i did try that and instead it only selects the 10th row — Collin, Jun 13 '16 at 14:02

score 0 · Answer 1 · answered Jun 13 '16 at 14:34

0

range(rows(selection.row-11),rows(selection.row-1)).copy

answered Jun 13 '16 at 14:34

Nathan_Sav

8,466
2
13
20

How to copy last 10 active rows instead of last active row

1 Answers1