locked
Are Range.Value2 & Range.PasteSpecial Paste:=xlPasteValues the Equivalent? RRS feed

  • Question

  • I'm curious if these two methods of copying range data are equivalent?  From what I can tell they are the same, but was wondering if any experts have noticed any differences or have a preference to which method is better?

    Sub CopyData()
    
        ' paste special values only
        Range("A1:B10").Copy
        Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
        ' copying data using Value2 property
        Range("C1:D10").Value2 = Range("A1:B10").Value2
        
    End Sub


    Ryan

    Thursday, April 3, 2014 4:32 PM

Answers

  • IMVHO, the First method,  gives you flexibility of what you wish to copy across. There are different options like xlFormulaAndNumberFormat, xlPasteValues etc.

    However the second method merely copies the values across, if it has a Format like Date or Currency it will not be carried across. The code above will do the same, but Copy and Paste will be more user driven and flexible of what needs to be carried across.

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Thursday, April 3, 2014 4:43 PM
  • If you only wanting to copy values the second method is certainly more efficient. I doubt you'd notice any the difference in speed between using Value/Value2,  but use Value2 if particularly you only want the intrinsic values of any Dates or Currencies. That said if the receiving cell are already formatted it won't make any difference.
    Thursday, April 3, 2014 5:25 PM
  • I don't think you'd notice any difference between Value/Value2 with percents  as the underlying data type is a double - are you sure you notice a difference and if so which XL version?

    However the Accounting format sets the cell with a Currency data type so you may indeed notice a difference. However when writing an accounting Value to an otherwise unformated cell it will arrive with a normal currency format, not the original accounting number format 

    Thursday, April 3, 2014 8:59 PM

All replies

  • IMVHO, the First method,  gives you flexibility of what you wish to copy across. There are different options like xlFormulaAndNumberFormat, xlPasteValues etc.

    However the second method merely copies the values across, if it has a Format like Date or Currency it will not be carried across. The code above will do the same, but Copy and Paste will be more user driven and flexible of what needs to be carried across.

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Thursday, April 3, 2014 4:43 PM
  • If you only wanting to copy values the second method is certainly more efficient. I doubt you'd notice any the difference in speed between using Value/Value2,  but use Value2 if particularly you only want the intrinsic values of any Dates or Currencies. That said if the receiving cell are already formatted it won't make any difference.
    Thursday, April 3, 2014 5:25 PM
  • I've noticed also that Value2 strips away all formatting from cells, not just for cells formatted for Dates and Currencies.  It also removes Percents, Accounting, Number.  Because of this inconsistency between Microsoft documentation and what tests I've run made me ask this question.

    Ryan

    Thursday, April 3, 2014 8:22 PM
  • I don't think you'd notice any difference between Value/Value2 with percents  as the underlying data type is a double - are you sure you notice a difference and if so which XL version?

    However the Accounting format sets the cell with a Currency data type so you may indeed notice a difference. However when writing an accounting Value to an otherwise unformated cell it will arrive with a normal currency format, not the original accounting number format 

    Thursday, April 3, 2014 8:59 PM