提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
注册账户
x

or

在Excel中使用Ctrl + V时如何将粘贴值设置为默认粘贴?

通常,在Excel中,当您从一个范围或工作表中复制数据并使用Ctrl + V粘贴到另一个时,格式化也将默认粘贴。 但是,有时候,当您粘贴数据时,您只想将数据粘贴为没有任何格式的值。 在这里,我可以为您介绍一种简单的方法,只需在Excel中使用Ctrl + V时将粘贴值设置为默认粘贴。

使用带VBA代码的Ctrl + V时,将粘贴值设置为默认粘贴


复制多个范围并一次粘贴到任何位置:

通常情况下,Excel不支持多个副本,但是,如果有 Kutools for Excel,其 复制范围 功能,您可以快速复制多个选定的范围,并将其粘贴到任何其他位置,只要您需要。

doc删除所有选定的11

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


使用带VBA代码的Ctrl + V时,将粘贴值设置为默认粘贴


如果仅将数据粘贴为值,则需要单击所选内容的角落处的小方块,并在每次粘贴数据后选择“值”选项。 以下VBA代码可以帮助您将默认粘贴更改为粘贴值。

1。 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications窗口.

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:使用Ctrl + V时,将粘贴值设置为默认粘贴

Sub PasteasValue()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

3。 然后保存并关闭代码,然后按 Alt + F8键 键打开 对话框。

4。 在 对话框中,选择使用的宏名称 PasteasValue 依次 期权 按钮,看截图:

文档粘贴值,默认1

5。 然后在 宏选项 对话框下 快捷键 部分,请输入 v ,看截图:

文档粘贴值,默认2

6。 然后点击 OK 关闭 宏选项 对话框,并继续关闭 对话。

现在,当你粘贴数据 按Ctrl + V,数据将默认粘贴为值。


相关文章:

如何将外部内容粘贴到Excel始终匹配目标格式?


演示:在Excel中使用Ctrl + V时,将粘贴值设置为默认粘贴

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!



推荐的Excel生产力工具

Kutools for Excel帮助您提前完成工作,并从人群中脱颖而出

  • 超过300强大的高级功能,专为1500工作场景设计,通过70%提高生产力,让您有更多时间照顾家庭和享受生活。
  • 不再需要记忆公式和VBA代码,从现在起让你的大脑休息一下。
  • 成为3分钟的Excel专家,复杂和重复的操作可以在几秒钟内完成,
  • 每天减少成千上万的键盘和鼠标操作,现在告别职业病。
  • 110,000高效人才和300 +世界知名公司的选择。
  • 60-day full功能免费试用。 60天退款保证。 2多年的免费升级和支持。

将选项卡式浏览和编辑带到Microsoft Office,远比浏览器的选项卡强大

  • Office选项卡专为Word,Excel,PowerPoint和其他Office应用程序设计:Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    loki · 23 hours ago
    Saved module - called "module 2" but nothing shows up in macros - no 'PasteasValue'. Seems like a step is missing?
  • To post as a guest, your comment is unpublished.
    Hakan · 1 months ago
    I want to learn if there is a way to work in a sheet, not in a module.

    If not, is there a way to work in particular sheet I determine?
  • To post as a guest, your comment is unpublished.
    Deyaa El Morsy · 4 months ago
    It is working but after pasting you cannot undo
    • To post as a guest, your comment is unpublished.
      Pankaj kumar · 2 months ago
      Same here.
      • To post as a guest, your comment is unpublished.
        skyyang · 2 months ago
        Hello, guys,
        I am sorry, may be there is no other good way for solving this problem, if you have any other good ideas, please comment here.
        Thank you!
        • To post as a guest, your comment is unpublished.
          fuat · 2 months ago
          Save as binary .xlsb,
          Then you can use ctrl+z with macros.
  • To post as a guest, your comment is unpublished.
    Ross Brown · 9 months ago
    I agree Graham, just tried this in Excel 2016. When I click the Run button in the Macro dialog, I get the error message:

    Run-tme error '1004':
    PasteSpecial method of Range class failed

    which is a shame as this really would have helped me today. I also tried Dejvid's macro below, but nothing happened when pasting from one book to another.
    • To post as a guest, your comment is unpublished.
      Benj · 5 months ago
      Make sure you have something selected in copy mode. dont even bother entering his code
  • To post as a guest, your comment is unpublished.
    Graham Gibby · 9 months ago
    Unless I missed something (and the instructions are *very* good), this does not work in Excel 2016.
    • To post as a guest, your comment is unpublished.
      fuat · 2 months ago
      I use Microsoft office Standard 2016, at it works.