copy a row and insert it elsewhere?

[anthonys]

Hi

I need to write a macro that will copy the 1st row of my spreadsheet and insert it into the row above the current users selection (which could eb anywhere).

I can insert a row above the users current seelction using:
Selection.EntireRow.Insert
i then need to copy the 1st row into this row, but when i copy row 1 it has lost the reference to the blank row and i can't paste it.

I think i need to dim a variable of the users current selected field so that i can reference it when pasting but i can't work it out.

Any help greatly appriciated!!

Cheers

 

[shades]

No need to select the Copy range. If we assume that the person has selected a Row (not just a cell on that row), then use this code:

    Rows("1:1").Copy
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False

________
HASH

 

[shades]

If the person selects only a cell, then use this code:

Sub Macro2()
    Dim lngRow As Long
    Rows("1:1").Copy
    lngRow = ActiveCell.Row
    Rows(lngRow & ":" & lngRow).Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

________
Bruin

 

[anthonys]

Thanks that is great!! I think i was trying to over complicate things as usual.

Cheers
Ant