-1

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
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Avin
  • 1
  • 1
  • 1
    Looks like you're pasting `xlPasteFormats`, not `xlPasteFormulas`. It's unclear what you're trying to accomplsh overall. Please check out he [tour] (you'll get your first badge!) as well as "[ask]". See the [help/on-topic] for more information about what's on topic on this site, and also important reading is **how to create a [mcve]**. You can [edit] your question to clarify what data you have (with examples), and what you're trying to do, as well as what you've tried and **where you're stuck". More [tips here](//codeblog.jonskeet.uk/writing-the-perfect-question/) from the site's top user. – ashleedawg Aug 09 '18 at 09:21
  • A value is the output of a formula, but only when there is a formula in the cell. If not, properties Value and Formula are the same for Excel. This means that what you want to dot can't be accomplished like you want to. Pasting Formulas will paste values too.The solution: I'm afraid you will need to loop all the cells in your selection, check if they start with `=` and then copy only those cells. – Foxfire And Burns And Burns Aug 09 '18 at 09:28
  • I want to keep format as well so I am using xlPasteFormats. xlPasteFormulas not working. It will copy those cells with values as well. – Avin Aug 09 '18 at 10:02

2 Answers2

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
DisplayName
  • 13,283
  • 2
  • 11
  • 19
  • :) This was my first question on Stack overflow and I appreciate the quick and correct response. Thanks a Lot. – Avin Aug 09 '18 at 10:06
0

You should do as below

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

Sasi

Bhushan
  • 114
  • 12