Copy Paste Special

[sj1601]

Hi All,

Is there keyboard shortcut for pasting values? I have to repeatedly perform this task and would save me a lot of time if I simply did ctrl+c and then the keyboard s/cut for paste value.

Thanks.

sj1601

 

[Zack Barresse]

Alt + E, S, V

HTH

 

[Cbrine]

sj,
I don't believe it's available as a regular shortcut, but I've add this code to my personal.xls and assigned the ctrl-Q as a shortcut to the macro.


Sub PasteValues()
On Error GoTo Errorhandler
Dim Test As String
Test = Selection.Address
Selection.PasteSpecial Paste:=xlValues
Exit Sub
Errorhandler:
Select Case Err.Number

Case 1004
Exit Sub
Case Else
MsgBox Err.Number & " " & Err.Description
Exit Sub
End Select

End Sub

Things to remember:
Since this is a macro, you will NOT be able to undo any pastes.
On occasion, it doesn't work(Not very often though), and it's never been a big enough problem for me to research why.

HTH
Cal

 

[sj1601]

Thanks for the smart suggestion firefytr. I am sure with practice I will be able to save a lot of time now.

Cbrine, though I would love to adopt your technique and check it out but I know nothing about macros. However, I am willing to give it a try if you could tell me how to do it.

Muchas Gracias.

sj1601

 

[Zack Barresse]

Another way would be to add it to your right click menu (which I setup for my wife). Let us know if you want code for that.

 

[sj1601]

Yes please. I would love that.

Also, please tell me how I would setup that code.

Thanks.

 

[Cbrine]

Zack,
I remember your code for that. Can you post a link to it for me? I always wanted to add it to my snippets.

sj,

To add a personal macro workbook(This is a workbook that opens in the background everytime you open excel, Great place to store generic macro's) just do this.

1. Right click on your toolbars and add the visual basic toolbar.
2. Press the red button to record.
3. In the dialog, change the store macro in: to
personal macro workbook.
4. Press OK.
5. Stop the macro.

This causes excel to create a personal macro workbook for you.
If you press Alt-F11 to open the vbe, you should see it in the project explorer.
6. Using the project explorer, add a new module.
7. Paste the code to this module.
8. Close the vbe and close excel, you should be prompted to save the personal macro workbook, make sure you click OK.
9. Open excel and click on the run macro button.
10. select the macro from you list(Should only have one)
11. Click on options.
12. Select your shortcut key here, and OK out of the dialog.

You should be in business now.

HTH
Cal

 

[sj1601]

Great. Before I do it, a few questions:

1. Is there a way to undo the whole activity? Just in case I screw up, I must know how to get out of it and restore status quo.
2. Will this feature work on every spreadsheet I work on(old and new)?
3. I work on lots of spreadsheets with macros built on them (I didn't write those). Will this code ever cause any "conflicts" with those macros?
4. Is this the same code that firefytr mentioned?

Thanks again.

sj1601

 

[Zack Barresse]

I don't have it now, so I had t rewrite it real quick...



<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> AppendRightClick()
    
    <SPAN style="color:#007F00">'Dimension variables</SPAN>
    Dim CB <SPAN style="color:#00007F">As</SPAN> CommandBar, cbItem <SPAN style="color:#00007F">As</SPAN> CommandBarButton
    
    <SPAN style="color:#007F00">'Reset menu so no duplicates are created</SPAN>
    <SPAN style="color:#00007F">Call</SPAN> ResetRightClick
    
    <SPAN style="color:#007F00">'Set variables</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> CB = Application.CommandBars("Cell") <SPAN style="color:#007F00">'right click (cell) menu</SPAN>
    Set cbItem = CB.Controls.Add(Type:=msoControlButton, before:=1)
    
    <SPAN style="color:#007F00">'<SPAN style="color:#00007F">Set</SPAN> button parameters</SPAN>
    <SPAN style="color:#00007F">With</SPAN> cbItem
        .Caption = "Paste &Values (Copy Mode)"
        .DescriptionText = "Paste Values"
        .FaceId = 387
        .OnAction = "Module1.PasteMyValues" <SPAN style="color:#007F00">'SHOULD MATCH EXACTLY!</SPAN>
        .TooltipText = "Paste Values"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    <SPAN style="color:#007F00">'Create another item</SPAN>
    Set cbItem = CB.Controls.Add(Type:=msoControlButton, before:=2)
    
    <SPAN style="color:#007F00">'Set button parameters</SPAN>
    <SPAN style="color:#00007F">With</SPAN> cbItem
        .Caption = "Leave &Static Values"
        .DescriptionText = "Leave Static Values"
        .FaceId = 454
        .OnAction = "Module1.LeaveStaticValues" <SPAN style="color:#007F00">'SHOULD MATCH EXACTLY!</SPAN>
        .TooltipText = "Leave Static Values"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ResetRightClick()
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    Application.CommandBars("Cell").Reset
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> PasteMyValues()

    <SPAN style="color:#007F00">'Dimension variables</SPAN>
    Dim c <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#007F00">'Test if a workbook is open or not</SPAN>
    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Or</SPAN> ActiveSheet <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#007F00">'Test application if anything has been copied</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "You must copy something first!", vbInformation, "ERROR!"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#007F00">'Leave values</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection
        c.PasteSpecial xlPasteValues
    <SPAN style="color:#00007F">Next</SPAN> c
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> LeaveStaticValues()
    
    <SPAN style="color:#007F00">'Dimension variables</SPAN>
    Dim c <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#007F00">'Test if a workbook is open or not</SPAN>
    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Or</SPAN> ActiveSheet <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#007F00">'Set values to be static</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    Selection.Value = Selection.Value
    
    <SPAN style="color:#007F00">'Message on errors</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Err <> 0 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "An error has occured!" & vbNewLine & vbNewLine & Err.Description, vbInformation, "ERROR!"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#007F00">'Leave values</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection
        c.Value = c.Value
    <SPAN style="color:#00007F">Next</SPAN> c
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



Let me know if this helps.

 

[sj1601]

Thanks firefytr.

Question: Do I perform the same set of steps that Cbrine has explained to incorporate this code?

Could you please address the questions that I have posted in my previous post with regards to your code?

Thanks.