Excel-insert cells and include formulas from above line

[oggsta]

I would like to insert cells and include formulas from above line, with one click of the button, as opposed to dragging each of the fromlas down from the above cell,any ideas on how to this would be appreciated.

 

[SkipVought]

Hi,

Try this...

Sub CopyLineAbove()
    Selection.EntireRow.Insert Shift:=xlDown
    With Selection
        .Offset(-1, 0).EntireRow.Copy
        .PasteSpecial _
            Paste:=xlPasteFormulas, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
    End With
End Sub

Hope this helps
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[oggsta]

Yes this was very helpful, the only problem is that the values in the cell are copied over; is it possible to have blank cells with only the formulas copied in to the inserted row.

Regards

 

[SkipVought]

How's this...

Sub CopyLineAbove()
    Selection.EntireRow.Insert Shift:=xlDown
    With Selection
        lrow = .Offset(-1, 0).Row
        icol = ActiveSheet.UsedRange.Columns.Count
        For Each rng In Range(Cells(lrow, 1), Cells(lrow, icol))
            With rng
                If Left(rng.Formula, 1) = "=" Then
                    rng.Copy Destination:=rng.Offset(1, 0)
                End If
            End With
        Next
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[oggsta]

Thanks very much

 

[SkipVought]

oggsta,

Keep in mind that using INSERT, might cause formulas below the inserted line to be off by one or more rows. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com