Ramblings

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” to streamline 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 – 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 speed up the process I headed to VBA to solve the problem macro-style!

You will want these macros to be available whenever you open Excel on your computer and regardless of which worksheet you access. 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 installing the macros here makes them available regardless of the workbook you’re in.

Below is the 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.

Sub PasteText()

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.

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!

[Disclaimer: I do not in any way claim to be the first to create such a macro. When researching this article it turns out there are plenty of other iterations around the internet]