I hate doing tedious work. Why should I spend minutes or even hours repeating the same action over and over again? If I can find a way to get the computer to do the work, I will. In programs like Microsoft Excel, Word, and PowerPoint, a macro is that way. Technically speaking, a macro is a series of actions that can be automated so you can quickly and consistenly execute them with the click of a button.
For example, I often need to paste information from a database into Excel. Unfortunately, after its pasted, the data is not formatted the way I want. And as shown below, it is ugly and hard to read.
Information that is hard to read after it was pasted into Excel.
To fix the data I need to do the following tedious steps:
First, select and bold the headings in row 1. You can use the mouse to select row 1, then press Ctrl B for bold.
Second, select all the data by pressing Ctrl A.
Third, turn off text wrapping. From the ribbon’s Home tab click Wrap Text twice. You need to click it twice because initially some cells have wrap text on and other have it off. The first click therefore turns wrap text on for every selected cell, and the second click turns wrap text off.
Fourth, autofit the width of each column. Assuming the data columns are still selected, from the ribbon’s Home tab choose Format, AutoFit Column Width.
Fifth, if any column is too wide, resize it with the mouse or select the column and use the Home, Format, Column Width… feature. You may also want to turn on wrap text (Home, Wrap Text) for that column.
Sixth, reselect all the data again (press Ctrl A), and then change the vertical alignment to top.
Finally press Ctrl Home to return the cursor to cell A1, and then freeze the top row so the column headings stay in place when you scroll down (View, Freeze Panes, Freeze Top Row).
As shown below, the results are much better.
Excel data that is now formatted correctly.
Unfortunately, the results took seven tedious steps. It would be much better if you could click one button and have the computer execute the seven steps for you. That is the beauty of a macro. You save time, and you know the computer will do the work the right way, every time.
Below is the macro code that will quickly execute those seven steps with one click of the button.
VBA Code to improve view of pasted data.
The next few TechMentor blog posts will show you how to plan, create, write, edit, and test macros. Also, if you would like some professional help creating Excel macros, please contact us.