Macro Conversion To Relative Cells

[Derek Hart]

I recorded a macro and got the following:

Range("A13").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A14").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Selection.ClearContents
Range("A14").Select

I want to make this a macro the user can run that does the following.
Whatever cell the user is on, it copies the row above to the row the user is
on. There are lists and data validations, so it copies all that over. But I
want all the values to be blank. Is there a better way to do this, using
relative paths (instead of A13), and not using copy and paste, and maybe a
way to do this without having to do ClearContents, so everything but the
text is copied. A newbie at Excel vba...

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToCopy As Range

Set myCell = ActiveCell

If myCell.Row = 1 Then
MsgBox "Not on this row, buddy!"
Exit Sub
End If

'copy the entire row?
Set RngToCopy = myCell.Offset(-1, 0).EntireRow
myCell.EntireRow.Insert
RngToCopy.Copy _
Destination:=RngToCopy.Offset(1, 0).Cells(1)

'clean up constants
On Error Resume Next 'just in case there are no constants
RngToCopy.Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0

End Sub