copying formulas, but not values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NewsMan - 06 Jan 2005 20:41 GMT

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

Reply to this Message

Ken Wright - 06 Jan 2005 20:58 GMT

Why don't you just use Data / Form?

Signature

Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

> 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
[quoted text clipped - 24 lines]
>
> End Sub

Reply to this Message

NewsMan - 06 Jan 2005 22:12 GMT

> 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.

Reply to this Message

Dave Peterson - 06 Jan 2005 23:41 GMT

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).

> 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
[quoted text clipped - 24 lines]
>
> End Sub

Signature

Dave Peterson

Reply to this Message