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
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
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.
Dick Kusleika:
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
19 October 2005, 8:53 amar.Value = ar.Value
Next ar
End Sub
Jon Peltier:
The .Values = .Values approach also doesn't clear the clipboard first.
19 October 2005, 4:49 pmMurray Shactman:
For a single area this seems to work:
Dim z As Variant
19 October 2005, 10:12 pmz = Selection
Selection = z
Juan Pablo González:
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.
19 October 2005, 11:16 pmHugo Domingos:
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?
20 October 2005, 5:00 amMurray Shactman:
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.
20 October 2005, 12:09 pmKen Hallinen:
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
24 October 2005, 6:31 amDave Peterson:
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.
24 October 2005, 4:03 pmKeith:
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.
28 September 2006, 7:02 amJon Peltier:
Keith -
It appears you're confusing Normal.dot, Word's default always-on template, with personal.xls, Excel's always-on personal macro workbook.
28 September 2006, 11:01 amHarald Staff:
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 ?
28 September 2006, 12:08 pmMikeC:
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!!
29 September 2006, 2:50 amPeter Jorgensen:
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?
18 October 2006, 4:29 amAndrew Adams:
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.
25 October 2006, 1:32 pmGates Is, You Know:
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.
11 August 2007, 12:45 pmSimon Herbert:
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:
.Style = "Normal"
.Value = .Value
End With