2

I need help to check this macro that intends to copy and paste without formatting. It doesn't work fine.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Target.PasteSpecial xlPasteFormulas
    Application.CutCopyMode = True
End Sub

How do I make Ctrl-V to paste without any format? I want to keep the excel clean and tidy, so users can copy and paste values and formulas without formatting.

Thanks!

Gerónimo Tutusaus
  • 37
  • 6
  • 3
    It's confusing, you want to use `Ctrl+V` or `Workbook_SheetSelectionChange`? – A.S.H Apr 17 '17 at 20:19
  • So... dates should be integers, percentages should be decimals (i.e. *floats* or *doubles*) and currency should only show relevant decimal places without monetary symbols? –  Apr 17 '17 at 20:19
  • related: [Should I turn .CutCopyMode back on before exiting my sub procedure?](http://stackoverflow.com/questions/33833318/should-i-turn-cutcopymode-back-on-before-exiting-my-sub-procedure). –  Apr 17 '17 at 20:20
  • 1
    If you look at the xlPasteType enumeration https://msdn.microsoft.com/en-us/library/office/ff837425.aspx, you'll see that there are many different combinations of things that can be pasted. Make sure you know exactly what you do and don't want pasted, and use the appropriate value. You many need to pasteSpecial more than once to get what you want. – Rich Holton Apr 17 '17 at 20:22
  • Ummmm ... What about users just using `Paste Special`. Since you reference `Ctrl+V` they are already using keyboard shortcuts. So `Alt+E+S` to load paste special is very simple *(or Alt+H+V` if you want to go by ribbon)*. From there they can select `Formulas`, `Values` amongst others. Not sure purpose of reinventing the wheel. – Scott Holtzman Apr 17 '17 at 20:38

3 Answers3

2

If you want to make your own, custom Ctrl-V, well, you can achieve it this way:

' Code Module ThisWorkbook
Private Sub Workbook_Open()
    Application.OnKey "^v", "ThisWorkbook.PasteWithoutFormat"
End Sub
Private Sub PasteWithoutFormat()
    If Application.CutCopyMode Then Application.Selection.PasteSpecial xlPasteFormulas
End Sub
A.S.H
  • 29,101
  • 5
  • 23
  • 50
0

Try this: Assigning Macros to Short-Cut Keys in Excel

You'll need to work out how users have access to the macro - there are a few ways to do this and I'm not so sure what will work in your environment, nor your access level to make these options work.

I suppose I'd build a very simple ADD-IN in which I'd embed the macro and there also, assign the macro shortcut to override CTRL + V. Then deploy the ADD-IN according to your company policies.

However, we need more details from you to fully implement the solution.

Bill Roberts
  • 1,127
  • 18
  • 30
  • 4
    Don't you think it is more like a comment rather than an answer? – M-- Apr 17 '17 at 20:23
  • I've added a few more details... but based on the question, not really sure what else to add since it feels to me at least, the link serves overall as the basis of the answer. Would you like me to delete the answer? – Bill Roberts Apr 17 '17 at 20:41
0

I went for the easiest way. I set up Ctr-v (in macro's options) to run the following code:

Sub Pstfrmul() 
    Selection.PasteSpecial xlPasteFormulas 
End Sub

Many many thanks for all your answers and comments. Best, Gerónimo

Gerónimo Tutusaus
  • 37
  • 6