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. The .Values = .Values approach also doesn’t clear the clipboard first.

3. For a single area this seems to work:

   Dim z As Variant
   z = Selection
   Selection = z

4. 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.

5. 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?

6. 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.

7. 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

8. 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.

9. 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.

10. Keith –

    It appears you’re confusing Normal.dot, Word’s default always-on template, with personal.xls, Excel’s always-on personal macro workbook.

11. 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 ?

12. 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!! :-)

13. 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?

14. 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.

15. 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.

16. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.