For anyone who works in analytics (or uses Excel more specifically), moving data from various sources into Excel can be a frustrating and arduous process. For those fortunate enough to find themselves in this position, Microsoft has provided us with “Paste Values” and “Paste as Text” so as to circumvent the folly that can be: formatting, formulae, hyperlinks, etc, etc…
Both of these features can save the user a lot of time – especially if you use Excel all day like me – with the only problem being that when you’re pasting hundreds of tables / cells / rows of data an hour, clicking through menus is not an ideal practice. To streamline the process I headed to VBA to solve the problem macro-style!
[Disclaimer: I do not in any way claim to be the first or last to create such a macro. When researching this article it turns out there are plenty of other iterations around the internet – this just happens to be the one I created and which works best for me]
In order to get the most out of them, you will want these macros to be available whenever you open Excel on your computer, regardless of which worksheet you’re accessing. To achieve this you need to install the macros into your ‘personal.xlsb’ file which can be found at ‘…Documents and Settings\*USERNAME*\Application Data\Microsoft\Excel\XLSTART\’ on your system. This “personal macro workbook” loads whenever you open Excel and so by installing the macros here they shall be at your beck and call; forever more.
Below is my VBA script for both “Paste Values” and “Paste as Text” macros (named appropriately). Open the personal.xlsb file, open Visual Basic Editor (Alt+F11), create a new Module (Alt>I>M) then rewrite the code below followed by the all important: Save. Alternatively, you can copy the script from here and then paste it into the module.
Macro to Paste as Text
Keyboard Shortcut: Ctrl+*LETTER*
ActiveSheet.PasteSpecial Format:=”Text”, Link:=False, DisplayAsIcon:= _
False
End Sub
———————————-
Sub PasteValue()
Macro to Paste Values
Keyboard Shortcut: Ctrl+Shift+*UPPERCASE_LETTER*
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Nb. In the above example please note the *LETTER* tag signifies where you need to select and input a letter for use as the macro shortcut. In my original code I use “q” and “Q” for the Text and Value macros respectively. The letter “g” is used by alternate versions of the macro around the internet.
Hopefully you find the code as valuable as I do. Let me know any ideas or past experiences you have with such macros in the comments below. Enjoy!
It‘s quiet in here! Why not leave a response?