Paste Special Formulas

[Francis Hookham]

Paste Special Formulas

I have a database with 100+ columns and several cells in each row have a
formula using data in other cells. I need to insert a row which has no data
but which does have the formula repeated in the formula cells. Using Paste
Special and choosing Formula, rather than All, I expected the data (Values)
would not be pasted in as well. But no, the values are being pasted in too

Is there a way of achieving what I want, apart from hiding a formatted row
out of sight and copying it the the formula, which is what I have reverted
to on previous occasions

Many thanks again

Francis Hookham

This is the recorded macro

Sub InsertRow()
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[J.E. McGimpsey]

Paste Special Formulas

I have a database with 100+ columns and several cells in each row have a
formula using data in other cells. I need to insert a row which has no data
but which does have the formula repeated in the formula cells. Using Paste
Special and choosing Formula, rather than All, I expected the data (Values)
would not be pasted in as well. But no, the values are being pasted in too

Is there a way of achieving what I want, apart from hiding a formatted row
out of sight and copying it the the formula, which is what I have reverted
to on previous occasions

Check out

http://www.mvps.org/dmcritchie/excel/insrtrow.htm