Thanks Tom, that worked. It's just a shame though that
Excel doesn't recognize the need to do this sort of thing
without VBA

-----Original Message-----
I included an inputbox to select the range since using
one to select the
destination cleared the clipboard.

Sub Convert_From_To_Values()
Dim rngSource As Range
Dim rng As Range
Dim Msg, Style, Title, Response
Msg = "Select rectangular range to copy using the mouse"
Title = "Copy Range"
On Error Resume Next
Set rngSource = Application.InputBox(Msg, Title, Type:=8)
On Error GoTo 0
If rngSource Is Nothing Then
MsgBox "You cancelled"
Else

Msg = "The selected cells were copied to the clipboard,"
& _
vbCrLf & _
"Now you should select a same sized area for the
pasting" & _
vbCrLf & _
" or select a single cell as upper left corner of
destination range" & _
vbCrLf & _
" use the mouse to select"
Title = "Copy and Message Help Box" ' Define title.
On Error Resume Next
Set rng = Application.InputBox(Msg, Title, Type:=8)
On Error GoTo 0

If Not rng Is Nothing Then
rngSource.Copy
rng(1).PasteSpecial xlValues
End If
End If
Application.CutCopyMode = False

End Sub

--
Regards,
Tom Ogilvy


"Bruce Roberson" wrote in
message
...
I already have a macro that converts a selection to
values, so this one will be different. This macro will
be
executed from a toolbar icon that is clicked while a
highlighted area is already selected unless you suggest
I
put a prompt in to select that first.

But here it is so far with the assumption that an area
is
already highlighted when this icon is clicked calling
this
macro.


Sub Convert_From_To_Values()
Selection.Copy


Dim Msg, Style, Title, Response
Msg = "The selected cells were copied to the
clipboard," _
& "now you should select a same sized area for the
pasting" ' Define message.
Style = vbOK ' Define buttons.
Title = "Copy and Message Help Box" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose OK.


' need a method here to pause till userhighlights
a selection'

Selection.PasteSpecial Paste:=xlValues
Else ' User chose No.
Application.CutCopyMode = False
End If
End Sub


Thanks for your suggestions,



Bruce


.

I know about that combination which is already on my menus
now. I just meant earlier I wish Excel combined that into
one function, rather than two.
-----Original Message-----
if you go to tools=Customize, then go to the second tab
and look through
the icons, there should be an icon under the Edit
category for Paste Values.
Drag that onto the menu bar of choice.

You would need to select a range, click the copy button,
select another
range, click the Paste Values button.

--
Regards,
Tom Ogilvy