Say you have a large contact list in Excel that is formatted, for example like: Column A – Names, Column B – Addresses, Column C – Cities, Column D – States, Column E – Zip Codes. It’s great that everything is formatted in neat columns, but it would be a nightmare if you wanted to use this list to print off envelope address labels.
Thankfully, there’s a macro can speed up this process. The macro below assumes that you’re starting in the left most column in your list (in my example, column A), and will insert three rows below (one for the address, one for the city, state and zip, and one blank one between records). It then takes the address from the second column (column B) and puts it below the name (row 2). It then proceeds to take the city, state and zip and puts them in the appropriate columns below the address (in row 3). Finally, it will move down to row 5, where the next record starts and repeats the process over again. It will continue to repeat (or loop) until it runs into a blank cell in column A. The result is a list of all your contacts formatted to be used on an envelope.
You’ll also notice that I added “Application.ScreenUpdating = False” to keep the screen from updating everytime the macro did something. If you have a list with a few hundred or even thousands of people it would get pretty obnoxious to see the screen flicker every time something was cut and pasted. At the end “Application.ScreenUpdating = True” is added to allow Excel to reveal the final masterpiece.
I only wish I thought of this macro when I was formatting my wedding invitation list!
Copy all the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.