Copy a Row and Paste only those Cells that contains Formula

Question

I have a named range. I am inserting a new row by code and resize the named range. Some columns contains formula, some contains values. I want to copy formula from the above cells. Below is my code:

Sub InsertRow()
    Dim lrInputRange As Long
    Set wbMacro = ThisWorkbook
    Set wsInput = wbMacro.Sheets("INPUT")
    'Insert new row at bottom
    lrInputRange = wsInput.Range("Input_Range").Rows.Count
    wsInput.Rows(lrInputRange + 1).EntireRow.Insert shift:=xlUp
    wsInput.Rows(lrInputRange).EntireRow.Copy
    wsInput.Rows(lrInputRange + 1).PasteSpecial xlPasteFormats
    wsInput.Rows(lrInputRange).EntireRow.Copy
    'Here I want to paste only those cells that contains Formulas          
    Application.CutCopyMode = False
    'Resize the range
    ThisWorkbook.Names.Add Name:="Input_Range", _
    RefersTo:=Range("Input_Range").Resize(Range("Input_Range").Rows.Count + 1)       
End Sub

Answer 1

use SpecialCells() to select cells without formula and clear them:

... your previuous code
wsInput.Rows(lrInputRange).EntireRow.Copy
wsInput.Rows(lrInputRange + 1).PasteSpecial
wsInput.Rows(lrInputRange + 1).SpecialCells(xlCellTypeConstants).ClearContents
'Resize the range
. rest of your code

Answer 2

You should do as below

wsInput.Rows(lrInputRange).EntireRow.SpecialCells(xlCellTypeFormulas).Copy Range.pastespecial xlpastevalues

Sasi