copying formulas, but not values

[NewsMan]

I have a spreadsheet where I need to continue to add new rows. I want
to be able to copy a row with all the formulas intact, but none of the
values that may be in the row being copied. For example, if I have my
cursor on row 1, which may already contain values, I want to be able to
copy Row 1 to Row 2 with the formuals, but not values that may already
be in Row 1. Here is my VBA code. It copies and inserts a new row and
the formulas, but it will also include any values. Any suggestions?

Sub Insert_Row()
Dim rnRow As Range

Application.ScreenUpdating = True

Set rnRow = ActiveCell

rnRow.EntireRow.Select

With rnRow
..Copy
..Offset(-1, 0).PasteSpecial (xlPasteFormulas)
End With

With Application
..CutCopyMode = False
..ScreenUpdating = False
End With

End Sub

 

[Ken Wright]

Why don't you just use Data / Form?

 

[NewsMan]

Why don't you just use Data / Form?

I would like to have it run off a macro so its easy for users (other
than me)to add a record without having to use data/form. But I find
attaching data/form to a macro is always buggy. I have used this VBA to
run data/form:

Sub ShowDataFormWithNewRecord()

SendKeys "+{TAB 6} "
CommandBars.FindControl(ID:=860).Execute

End Sub

It works most of the time, but you have to make sure all year numbers
are typed (i.e. 2005 not 05) or it will give an error, that the cursor
is in the right row plus it will sometimes overwrite a record that is
similar.

 

[Dave Peterson]

David McRitchie has a macro that does what you want.

It even asks how many rows to insert.

http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

He copies the whole row, but then wipes out the constants (IIRC).