The Paste Special command is a good example.  This exercise records the Paste Special command in order to give us a command to work with (to break the command into more than one line).  This exercise will also review the Paste Special command, review the =PROPER command, review autofill and other related VBA topics.

This article is written in a step-by-step format and it introduces other Excel concepts as it proceeds.  For the short version of this article, click here.

Step-by-step:
1. Open a new workbook and type  someone's name 1  in cell A5
    Type it all in lower case

2. Make column wider, if necessary
    In order to better view the entered text.

3. Drag the cell with the crosshair cursor down to cell A10
   You should now have:  someone's name 1,  someone's name  2, etc.
    in the cells A5:A10

4. In cell  B5, enter the function:  =Proper(A5)
    This function converts text to its Proper case, i.e. the first letter
     of each word  is capitalized.  The  =UPPER and  =LOWER 
     functions are related commands.

5. Copy  B5  down to  B10

6. At this point you desire to copy the Proper text back into Column A. 
    But since Column B is a function you must copy the Values
    into Column A, not the function.

7. Select the text in cells B5:B10

8. Click on  Tools  (in the menu), then
       Macro Record   Record New Macro
            To turn the macro recorder on

9. Name the macro  PasteSpecialValues  and click on  OK
     Remember: no spaces in macro names.

10. Click on the  Copy  button 

11. Click on cell  A5

12. Click on  Edit  Paste Special  (in the menu)
     Do not select the regular Paste selection in the menu or
     the Paste button.

13. Click on  Values

14. Click on  OK
    The Values have now been copied into column A.

15. Press  Escape
    To clear the clipboard

16. Click on the  Stop Recorder  button
    The macro is now recorded.  Click on  Module1  tab to review what 
      has been recorded.  (The numbered lines are for reference only.) 
      The macro should look something like :

1   '
2   'CopyPasteSpecial Macro
3   '
4   Sub CopyPasteSpecial()
5    Range("B5:B9").Select
6    Selection.Copy
7    Range("A5").Select
8   Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, _
9         SkipBlanks:=False, Transpose:=False
10   Range("A5").Select
11   Application.CutCopyMode = False
12  End Sub

Explanation:

8 & 9 --This is the meat of this exercise.  Note than Excel broke the command into two lines. The underscore character causes Excel to continue to read on the next line as if it was on the same line.  The simple rule for breaking lines is to place the underscore character after a space that normally displays in the command.  In some cases, such as in this macro, the macro recorder does this for you.

By definition, the Paste Special command is long as it deals with all the items in the Paste Special dialog box.

11 --This command clears the clipboard.  The marquee is no longer displayed around the data that is being copied.  It is entered by Excel when you press ENTER to complete a Paste command or when you press Escape to clear the Clipboard.

1, 2, & 3 --All lines that begin with the apostrophe (  '  ) are comments and have no effect on the macro.

2 --The name of the macro as entered by the recorder, but you can change it to read anything you want or even delete the line.
 

To break the Paste Special command into several additional lines, making it even easier to read:

1. Place the cursor immediately in front of the  P  in Paste:

    Selection.PasteSpecial | Paste:=xlValues, Operation:=xlNone, _

    The |  represents the cursor. Make sure there is a space after PasteSpecial

2. Type the Underscore character  (  _  )  and press  Enter
    Pressing Enter created a new line and the remaining portion of this
    command has been pushed down one line.

3.  Repeat these steps until this portion of the macro reads:

Selection.PasteSpecial  _
Paste:=xlValues,  _
Operation:=xlNone,  _
SkipBlanks:=False,  _
Transpose:=False

Review
It is sometimes desired to break a long macro into two or more simply to make it easier to read. The macro recorder broke this command into two lines as it recorded the macro.  This is for illustrative purposes only; we broke the same line into several more lines. There is no right or wrong way to display a macro, whatever works best for you.
 

Breaking up a macro text string into more than one line.

Let's assume you desire for a macro to enter some text on the worksheet for you and you desire than all the text be placed in one cell (show on one line) in the spreadsheet, but as with the macro above you desire to look at it in two or more lines in the Module.

Go to the bottom of your Module and type the following macro.
Line 3 is to be typed all on one line in the Module and is surrounded in quotes. Don't type the line numbers:

1  Sub EnterFootnote ( )
2   ActiveCell.FormulaR1C1 = _
3   "Please refer to Page 18 of this report
      for a detailed explanation of the variances
      shown on this page."
4  End Sub

2   The ActiveCell.FormulaR1C1  is the command to make an 
     entry directly into a cell.

2   Note the  = ,  then a space ,   then the _  (underscore character)

At this point, the macro is in two logical lines, the VBA command and the data associated with the command

Select a sheet and run the macro to ensure it works.

Now let's break the text string into two lines.  This must be done by concatenating two strings together.
 

Edit the macro to look like:

1  Sub  EnterFootnote ( )
2   ActiveCell.FormulaR1C1 = _
3    "Please refer to Page 18 of this report for detailed " &  _
4    "explanation of the variances shown on this page."
5   End Sub

3 --Breaking a text string into two lines is done by concatenating two text strings together. Note the text string portion of line 3 ends with a double quote ( "  ).  The space before the double quote is the space that normally follows the word detailed.

3 --The ampersand character  (  &  ) says that we are going to add 
    anothertext string to this text string.
     Note the  & ,  then a space,  then the  _  (underscore character)

4 --This line is the remaining text, surrounded by quotes.

When the macro runs, lines 3 & 4 are considered to be just one long text string.

Select a sheet and run the macro. 
  It should run just as it did before
 
 
 

Back to Top
 
 
 
 
 
 
 
 
 
 
 

Drag the cell with the crosshair cursor

• Move the mouse pointer to the lower right of the cell
• When it covers the auto fill handle, the mouse pointer changes to

  a cross hair in shape (some users call this shape plus)
• While the mouse pointer in in the shape of the a cross hair, click and 

   drag down
• If the mouse pointer is in the shape of an arrow, you will move 

   the cell, you want the crosshair shape
In this exercise, this invokes the autofill feature
 
 
 
 
 
 
 

Back
• When the macro starts, the Stop Recorder Toolbar displays
• Sometimes it fails to display
• If it is not displayed, simply click on View Toolbars (in the menu) 

  and click on Stop Recorder
• It should now be displayed on the screen
• If you do this with the recorder on, these commands will be 

  recorded as well.  If so, simply delete them from the recorded macro