microsoft visual basic for applications excel paste values

Remember, we don’t want to capture any cell selections or Copy actions. Cell B1 holds a different value and has no other formatting. Cell A1 has been formatted with Bold and colored with a blue background.

This isn’t strictly necessary, but it’s useful to make sure we captured the correct behavior. I built a couple cells to demonstrate the effect of Paste Values. Once we press Record, Excel’s Macro Recorder will track every click and every keystroke we make, and we only want it to remember the Paste Values command. We are going to replicate a basic Paste Values action, but that means we need to get ready for it. You should see the Developer menu tab appear along the top of the Excel window. In the Excel Options dialog box, choose the Customize Ribbon option on the left menu, and under the right column labeled Customize Ribbon, check the Developer option.

The macros menu isn’t turned on by default, so before we begin, we need to enable the Developer tab in Excel settings.Ĭlick on File and choose Excel Options at the bottom of the menu. Macros are recordings of your actions in Excel that can be duplicated later. To make custom shortcuts, we need to write a bit of VBA code, but fortunately, we don’t need to learn VBA just to duplicate an existing Excel function. Here, we’ll show you how to make your own custom keyboard shortcuts with macros by giving Paste Values the convenient CTRL+SHIFT+V it should have had all along… Enabling the Macros Menu and Developer Tab Paste Values, for example, is one of the most common actions when formatting spreadsheets, but the only keyboard access for it is the cumbersome ALT+H+V+V through Excel Key Tips. Unfortunately, not every common action in Excel has a keyboard hot-key assigned to it by default. I already showed you my top 10 keyboard shortcuts, and you can get a free quick reference for them when you join the newsletter. We all know that keyboard shortcuts are the fastest way to accomplish most tasks in Excel.

7Making CTRL+SHIFT+V Work For External Paste Operations.

1Enabling the Macros Menu and Developer Tab.

Microsoft visual basic for applications excel paste values