Automating pastevalues

There are a lot of good comments on the mouse shortcut entry to Paste Values. I am curious though, because I haven’t seen one variation of a macro to Paste Values that I thought was more straightforward.

The commented method involves two steps, one, to copy the range, and two, to perform the Paste Special, something like:

Sub CopyPasteValues()
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues
End Sub

The other alternative, is to use the .Value property of the range, like this:

Sub ValueToValue()
    Selection.Value = Selection.Value
End Sub

So, what’s your take, is one better than the other? In theory I would think that the second one would be faster. It’s only one operation, and Excel doesn’t have to keep the range in memory to perform the paste special. I’m not sure how either method works with discontinuous ranges.

Posted in Uncategorized

16 thoughts on “Automating pastevalues

  1. It looks like the PasteSpecial method doesn’t work on multiple areas. Also, the ValueToValue gives incorrect results when .Areas.Count is greater than 1. I agree that ValueToValue should be the more effecient method. Since neither work with multiple areas, looping through the areas seems to be the best way

    Sub ArVtoV()
    Dim ar As Range

    For Each ar In Selection.Areas
    ar.Value = ar.Value
    Next ar
    End Sub

  2. Murray,

    I think that is the same as just doing

    Selection.Value = Selection.Value

    you’re using the default property of the Selection object (in this case, a Range object), to assign it to the z variable, and then assign it back, so you can do it in one step.

  3. How can I make sure that this macro applies to all the workbooks which I will open in the future rather than only that particular workbook in which I created the macro?

  4. I think you are right.

    That appears to work as does:

    Selection = Selection.Value

    When I used the copy and pastevalues method on large ranges it appeared slower than your method of:

    Selection.Value = Selection.Value

    I was copying values from one worksheet to a worksheet in another file.

  5. I’ll second the value = value suggestion. I stumbled across it when I was actually bombing excel trying to use the copy – paste.special values in an application I was building

  6. One of the differences I’ve seen between “.value = .value” and “copy|paste special values” occurs when a range contains constants that have been formatted character by character.

    (Sometimes the selection will contain both constants and formulas.)

    The “.value = .value” version is the one that kills that kind of formatting.

    Just a thought if you want to convert all the cells on a worksheet in one command.

  7. To answer Hugo’s question (from a year ago) for those who don’t know… make sure that the VBA module in which this code resides is located in the Normal.xls file instead of the file you’re working on. Normal.xls is the default file that is used whenever Excel opens.

  8. My norwegian Excel 2000 defaults to Global.xls. My US Excels 2000 and 2k3 default to Personal.xls. I’d think names Global and Personal indicate different scopes, but I havent found any litterature on it. Is it just a reginal translation thing ?

  9. Generally, I would use Juan’s first method within a large section of code, which I do a lot due to the sort of thing I have to churn out.

    For “one-off” instances of pasting values within a sheet (outside of running code), I added “Paste Values” as a right-click option – I prefer this to a keyboard shortcut (but I like the mouse. I wear out at least 2 a year…). The code to do so was snagged from somewhere, not sure entirely where (it was some time ago and I’ve eaten, drunk beer and slept since then!) so unfortunately I can’t credit the original poster (believe it was one of the gurus from the Excel Programming community at Microsoft.com)

    Run the following *once* (running more than once will add multiple occurrences of the option to your right-click, as I found out to my cost!!) and Paste Values will appear as a right-click option.

    Public Sub AddPV()
    Dim iCtr As Long
    iCtr = Application.CommandBars(“Cell”).FindControl(ID:=22).Index
    Application.CommandBars(“Cell”).Controls.Add Type:=msoControlButton, ID:=370, Before:=iCtr + 1
    End Sub

    Just in case anybody DOES make the mistake I did (and ends up with 37 Paste Values options….), the following will sort you out – it will remove one instance of Paste Values on each run:

    Public Sub TakeItOff()
    Set lbl = CommandBars.FindControl(Type:=msoControlButton, ID:=370)
    lbl.Delete (False)
    End Sub

    Finally, apologies if I’m teaching any grannies around here to suck eggs!! :-)

  10. This may or may not be a dead discussion but this appears not to work if you have charts/pivot tables on a spreadsheet. I’m trying to automate reports and using Copy/PasteSpecial gets to the point where its using huge amounts of memory while trying to remove the formulas from worksheets so that when we distribute the report all they have is the values, but using Selection.Value = Selection.Value (or any of the variations suggested on this page) I get an application-defined or object-defined error (Error code 1004). Is there anohter way to automate pastevalues without looping through each cell?

  11. I was just doing a copy pastespecial on a pivot table and I found the value method worked if my selection was just the data and the headings in the pivot table. The old way I had before was to copy ( the same selection only data and headings) to the columns next to the pivot and then removing the pivot table columns.

  12. A corollary to Dave’s sole post in this (undying?) thread about custom formats: Leading zeroes perish from .value=.value . A1=RIGHT(1039,3)’s “039? becomes 39 in General format.

  13. Gates (and Dave) – that is why I prefer .value = .value

    I work with a lot of data that is imported as text. Using this code I can quickly change to numeric format.

    I also use this to remove unwanted formating at the same time:

    With Selection
       .Style = “Normal”
       .Value = .Value
    End With

Leave a Reply

Your email address will not be published. Required fields are marked *