What separates the talented individual from the successful one is a lot of hard work and study; a constant process of honing.

Stephen King

You cannot do a kindness too soon, for you never know how soon it will be too late.

Ralph Waldo Emerson (1803-1882)

Activating a sheet in VBA

Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.

Avoiding danger is no safer in the long run than outright exposure. The fearful are caught as often as the bold.

Helen Keller (1880–1968)

Everything can be taken from a man, but one thing, the last of the human freedoms – to choose one’s attitude to any given set of circumstances, to choose one’s way.

Viktor Frankl, Nazi concentration camp survivor

Close Existing File

To close the existing file you can use

Ctrl + w

If you want to save and close you can use on after the other.

Ctrl + s
Ctrl + w

The s and w keys are close to each other, so it can be a quick way to save and close a file with one hand.

Save and Close

Useful Formatted Table shortcuts

These apply to a formatted table (Format As Table icon used from Home ribbon – see below)

Ctrl + a Selects the table data – no headings

Ctrl + + inserts a new blank row in the table
Ctrl + – (minus) deletes a row from the table

Ctrl + Space selects all the data in a column – no heading
Shift + Space selects all the data in a row

The shortcut below works in any table formatted or not

Ctrl + * to select the table (use the * on the numeric keypad)

Excel 2013 Power Programming with VBA (Wiley)

This is the third version of this book that I have bought. I own XL 2000, XL 2007 and now this one. It’s worth updating as new features are added in each Excel version.

I highly recommend this book if you have been using macros for a while and want to take them to another level. It is not a step by step instruction guide you need to read and absorb.

It demonstrates and explains many best-practice techniques but it can take time to digest the ideas. It has a companion website with the examples from the book.

(John Walkenbach is my favorite Excel author and I was honoured that he wrote a recommendation that appears on the back of my own book)

Copy As Picture

When copying between Excel and Word or PowerPoint, in some cases you may want to copy the range as a picture. This has the advantage of being easier to re-size and manipulate in Word or PowerPoint.

Select the range first and then use the drop down on the Copy icon on the Home ribbon – see below and select Copy as Picture

A dialog will display allowing you to specify the type of picture to create.

Click OK to accept the defaults

You can then paste into the other application and treat it like an image.