locked
VBA Copy Range as Values RRS feed

  • Question

  • Hi All,

    I am trying to copy a range(5 cells) to another range by only providing one cell reference. For e.g i am setting

    Copyrng= activesheet.range("A10:A14")

    Destirng= cell.offset(0,1) ' cell is a cell i am getting using find method

    copyrng.copy cell.offset(0,1) works perfect but if my copyrng has formula it copies formula also

    what shall i change in above line of code to copy only values.

    Thanks,

    Zav

    Tuesday, January 26, 2016 7:47 PM

Answers

  • Copyrng.copy
    Destirng.PasteSpecial Paste:=xlPasteValues

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Wednesday, January 27, 2016 2:10 AM
    • Marked as answer by zaveri cc Wednesday, January 27, 2016 2:43 PM
    Tuesday, January 26, 2016 9:10 PM

All replies

  • Copyrng.copy
    Destirng.PasteSpecial Paste:=xlPasteValues

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Wednesday, January 27, 2016 2:10 AM
    • Marked as answer by zaveri cc Wednesday, January 27, 2016 2:43 PM
    Tuesday, January 26, 2016 9:10 PM
  • It works, great. Thanks.
    Wednesday, January 27, 2016 2:43 PM
  • What if copyrng or Destirng are merged cells?
    Wednesday, January 27, 2016 5:33 PM
  • Try changing

    Copyrng.copy

    to

    Copyrng.MergeArea.copy


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 27, 2016 8:07 PM
  • Copyrng.MergeArea.copy throws- application defined or object defined error.

    Coyrng have unmerged cells but copy destination is having merged cells

    Copyrng.copy
    Destirng.MergeArea.PasteSpecial Paste:=xlPasteValues

    also gives same error

    Wednesday, January 27, 2016 8:40 PM
  • I'd avoid merged cells. As you have found, they cause problems when you try to use them in VBA.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 27, 2016 8:55 PM