Changing the _default_ Paste options to paste cell contents only?

M

Michael Baglio

Is there a way to change the default settings in the Paste operation
to paste _only_ the cell contents-- values,formulas-- _without_
pasting the formatting?

Clicking the "paste special" and manually choosing either "values" or
"formulas" is getting old...

I don't see an option in the "options" menu. ;>

Appreciate your help,
Michael
 
W

William

Hi Michael

Here is one possibility...

Copy the sub below into the ThisWorkbook module of your Personal.xls file.
The code creates 2 new options on your "Edit" menu to copy the selected
range as values or formulae.

Private Sub Workbook_Open()
On Error Resume Next
With Application.CommandBars("Worksheet Menu Bar").Controls("Edit")
..Reset
..Controls.Add Type:=msoControlButton, ID:=2950, Before:=8
..Controls(8).Caption = "Paste Values"
..Controls(8).Style = msoButtonIconAndCaption
..Controls(8).OnAction = ThisWorkbook.Name & "!vals"
..Controls(8).FaceId = 22
..Controls.Add Type:=msoControlButton, ID:=2950, Before:=8
..Controls(8).Caption = "Paste Formulae"
..Controls(8).Style = msoButtonIconAndCaption
..Controls(8).OnAction = ThisWorkbook.Name & "!formlae"
..Controls(8).FaceId = 22
End With
End Sub


Copy the 2 subs below into a general module of your Personal.xls file.

Sub vals()
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Sub formlae()
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

To copy a range as values or formulae, select the range you want to copy,
right click and select "Copy" - then select the first cell of the
destination range and from the "Edit" menu, select "Edit>Paste Values" or
"Edit>Paste Formulae".

--
XL2002
Regards

William

[email protected]

| Is there a way to change the default settings in the Paste operation
| to paste _only_ the cell contents-- values,formulas-- _without_
| pasting the formatting?
|
| Clicking the "paste special" and manually choosing either "values" or
| "formulas" is getting old...
|
| I don't see an option in the "options" menu. ;>
|
| Appreciate your help,
| Michael
 
M

Michael Baglio

Hi Michael
Here is one possibility...

Copy the sub below into the ThisWorkbook module of your Personal.xls file.
The code creates 2 new options on your "Edit" menu to copy the selected
range as values or formulae.
Click to expand...
snip...

William,

First, thank you for taking the time to assist.
However, if I understand your suggestion, I'm still forced to _make a
choice_ of how information is pasted. I already have a mechanism to
use if I want to make a choice not to paste the original cell's
formatting; I can use the "paste special" option and choose "values"
or "formulas".

Now, I'm a marketing guy, not an excel head, so you already know I'm
not the brightest bulb in the chandelier, but it seems _ludicrous_ to
me that if I want to paste _only_ the information from one cell to
another and always keep the destination cell's formatting in tact,
there isn't a _default_ option within the options menu to allow me to
set the paste _default_ to paste _only_ the information (whatever it
is-- value or formula), leaving the destination cell's formatting
alone. (???)

Gawd I hope I'm missing something simple here...

Thanks again,
Michael
 
W

William

Hi Michael

I was attempting to create 2 additional "default" options on the "Edit"
toolbar, one for pasting values and one for pasting formulae - in both cases
leaving the formatting of the destination cells unchanged. It should be as
simple as using the usual Excel default of "Edit>Paste" which, as you know,
copies everything.

Am I missing something or have I misunderstood you?
--
XL2002
Regards

William

[email protected]

| On Thu, 6 Jan 2005 07:53:34 -0000, "William" <[email protected]>
| wrote:
|
| >Hi Michael
| >Here is one possibility...
| >
| >Copy the sub below into the ThisWorkbook module of your Personal.xls
file.
| >The code creates 2 new options on your "Edit" menu to copy the selected
| >range as values or formulae.
| snip...
|
| William,
|
| First, thank you for taking the time to assist.
| However, if I understand your suggestion, I'm still forced to _make a
| choice_ of how information is pasted. I already have a mechanism to
| use if I want to make a choice not to paste the original cell's
| formatting; I can use the "paste special" option and choose "values"
| or "formulas".
|
| Now, I'm a marketing guy, not an excel head, so you already know I'm
| not the brightest bulb in the chandelier, but it seems _ludicrous_ to
| me that if I want to paste _only_ the information from one cell to
| another and always keep the destination cell's formatting in tact,
| there isn't a _default_ option within the options menu to allow me to
| set the paste _default_ to paste _only_ the information (whatever it
| is-- value or formula), leaving the destination cell's formatting
| alone. (???)
|
| Gawd I hope I'm missing something simple here...
|
| Thanks again,
| Michael
 
M

Michael Baglio

Hi Michael

I was attempting to create 2 additional "default" options on the "Edit"
toolbar, one for pasting values and one for pasting formulae - in both cases
leaving the formatting of the destination cells unchanged. It should be as
simple as using the usual Excel default of "Edit>Paste" which, as you know,
copies everything.

Am I missing something or have I misunderstood you?
Click to expand...

No, you didn't misunderstand, I just didn't do a good enough job of
asking for what I really want. :)

I'm a Control-C and Control-V kind of guy. I'm looking for an option
that allows me to hit Control-V and have the paste operation paste
_only_ the contents of the original cell without changing the
formatting of the destination cell.

Seems reasonable to me that the _default_ operation would be to paste
only the contents, leaving the destination cell's formatting alone.
Aparently, I'm just wrong. ;> Excel seems hell bent to make me go
through more than one keystroke/mouse click to do what I want to do,
and doesn't even allow me a way to over ride it.

I've even looked in the "Customize" box of the Tools menu. I can add
a seperate command for "Paste Values" to the edit menu, I can even add
that icon to a toolbar, but I can't seem to find a way to make this
program understand that when I press Control-V I want to paste _only_
the contents _unless_ I instruct it otherwise.

"Control-V" equals "paste contents _only_." Should be simple. What
am I not seeing?

Michael
 
W

William

Michael

As far as I am aware there is no "default" mechanism within Excel to do what
you want.

--
XL2002
Regards

William

[email protected]

| On Thu, 6 Jan 2005 15:04:04 -0000, "William" <[email protected]>
| wrote:
|
| >Hi Michael
| >
| >I was attempting to create 2 additional "default" options on the "Edit"
| >toolbar, one for pasting values and one for pasting formulae - in both
cases
| >leaving the formatting of the destination cells unchanged. It should be
as
| >simple as using the usual Excel default of "Edit>Paste" which, as you
know,
| >copies everything.
| >
| >Am I missing something or have I misunderstood you?
|
| No, you didn't misunderstand, I just didn't do a good enough job of
| asking for what I really want. :)
|
| I'm a Control-C and Control-V kind of guy. I'm looking for an option
| that allows me to hit Control-V and have the paste operation paste
| _only_ the contents of the original cell without changing the
| formatting of the destination cell.
|
| Seems reasonable to me that the _default_ operation would be to paste
| only the contents, leaving the destination cell's formatting alone.
| Aparently, I'm just wrong. ;> Excel seems hell bent to make me go
| through more than one keystroke/mouse click to do what I want to do,
| and doesn't even allow me a way to over ride it.
|
| I've even looked in the "Customize" box of the Tools menu. I can add
| a seperate command for "Paste Values" to the edit menu, I can even add
| that icon to a toolbar, but I can't seem to find a way to make this
| program understand that when I press Control-V I want to paste _only_
| the contents _unless_ I instruct it otherwise.
|
| "Control-V" equals "paste contents _only_." Should be simple. What
| am I not seeing?
|
| Michael
 
M

Michael Baglio

As far as I am aware there is no "default" mechanism within Excel to do what
you want.
Click to expand...

Thanks again for your help, William. I'll use your previous post and
see if it helps. Appreciate it.

Michael
 
D

Dave Peterson

William mentioned the icon that you could add to your toolbar (I'd do that just
in case I ever wanted to use it).

But you could "push" that icon via a macro assigned to a shortcut key.

I chose ctrl-shift-v (upper case V).

You could add this code to your existing personal.xls workbook, but since you're
not an excel head, I'm guessing that you don't have an existing personal.xls
workbook.

You could create a new workbook
hit alt-f11 to see the VBE (where macros live)
hit ctrl-r to see the project explorer
rightclick on your project (should be like "VBAProject (book1)")
select Insert|Module

Then paste this in:

Option Explicit
Sub auto_open()
Application.OnKey "^V", "PasteMyValues"
End Sub
Sub PasteMyValues()
On Error Resume Next
Application.CommandBars.FindControl(ID:=370).Execute
End Sub

Now alt-f11 back to excel.
File|SaveAs
Name: PasteValuesWorkbook
but make "save as type"
"Microsoft excel add-in (*.xla)"

Save it to a nice memorable spot (anywhere)

Close this workbook.

Now, click
tools|addins
browse for that workbook you just saved and select it.
You should have a checkmark in front of it.

Test it out.

Close excel, reopen it and try it again.
 
D

Dave Peterson

Ps. You'll find that lots of macros will kill the undo stack. I'd use the
button on the toolbar.
 
M

Michael Baglio

You could add this code to your existing personal.xls workbook, but since you're
not an excel head, I'm guessing that you don't have an existing personal.xls
workbook.
Click to expand...

Well, I will now. ;> Thanks!

Michael
 
D

David Howdon

Dave said:
William mentioned the icon that you could add to your toolbar (I'd do that just
in case I ever wanted to use it).

But you could "push" that icon via a macro assigned to a shortcut key.

[snip]



Option Explicit
Sub auto_open()
Application.OnKey "^V", "PasteMyValues"
End Sub
Sub PasteMyValues()
On Error Resume Next
Application.CommandBars.FindControl(ID:=370).Execute
End Sub
Click to expand...

[snip]

I've been wanting to do something similar to this myself but for "paste
formulae" rather than "paste values". How would I need to modify this
macro to make it execute the paste formulae function that was defined
earlier in this thread.

I thought it would be something do do with changing the "ID:=370" bit to
some other reference but since I could not see where the number 370 came
from I couldn't work out what to change.

Thanks
 
D

Dave Peterson

I looked in Tools|customize|Commands Tab|Edit category.

I didn't see an button that does Edit|pastespecial|formulas.

But you could have a macro that is assigned to the shortcut key of your choice
(I still chose ctrl-shift-V).

Option Explicit
Sub auto_open()
Application.OnKey "^V", "PasteMyFormulas"
End Sub
Sub PasteMyFormulas()
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteFormulas
If Err.Number <> 0 Then
MsgBox "An error occurred: " & Err.Description
End If
On Error Goto 0
End Sub



David Howdon wrote:
 
Top