Insert row with formulae without contents

[camlad]

Catastrophe! I did not keep a copy of a macro which inserted a row above and
copied everything from the row below into the new row except any contents and
fill color. The formula in some of the cells was copied but none of the
contents so the new row was ready for entries

One of you helped with this several years ago and it has been very useful –
please help again.

 

[Bob Phillips]

Sub InsertNewRow()
ActiveCell.EntireRow.Insert
Rows(ActiveCell.Row + 1).Copy
Cells(ActiveCell.Row, 1).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

 

[camlad]

Thanks Bob - this was the trouble I had last time - the macro copies
everything - I want a clear row with only the formula which is in some of the
cells - all others empty and without any contents or fill (apart of course
from the formula) which the cell below might have.

?

camlad

 

[Nikki]

Is there a macro that will copy the row above into only highlight cells.
Would I change the +1 to -1? Can you please help. I have a subtotal function
that I would like to fill the blanks with the row above for only visible
blank cells. Help please....

 

[camlad]

Bob, I have found the macro I had lost but, having pasted it into the
PERSONAL.XLS module it will not run - have I missed something when I copied
it into the Word doc where I saved it?

I do hope you can help - it is imortant for me to get it going soon and it
is way beyond me - as you can see it is the work of others.

‘Insert row reproducing formulae from row below

Sub InsertRowsAndFillFormulas(Optional vRows As Long)
Application.ScreenUpdating = False
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <[email protected]>
'Dim vRows As Integer (This put at the beginning of this Module
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee
2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
Application.Goto reference:="R[1]C1"
End Sub

Thanks

camlad