Moving around the Worksheet in VBA for Excel

Other methods (Delete, ClearContents, Paste and PasteSpecial) plus errors

Delete or ClearContents

BEWARE: If you write Range("a2").Delete the cell A2 is destroyed and cell A3 becomes cell A2 and all the formulas that refer to cell A2 are scrapped. If you use Range("a2").ClearContents only the value of cell A2 is removed. In VBA Delete is a big word use it with moderation and only when you really mean Delete.

Copy/Paste

In this piece of code you are copying cells A1:A5 to B1:B5. Notice that you don't have to select cells B1 to B5 to paste just selecting B1 will do. Every time you copy/paste don't forget to empty the clipboard.
Range("A1:A5").Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode=False
Notice and remember that when you paste you paste in ActiveSheet not ActiveCell. On the contrary for PasteSpecial you paste on Activecell, Selection or Range("So andSo")

Instead copying from one cell, moving to another cell and then pasting you can "call" the content of a cell or a range into the Activecell:
Range("V1:V5").Copy Destination:=ActiveCell
Sheets("MySheet").Range("V1:V4").Copy Destination:=ActiveCell

See Copy/PasteSpecial in the "Precautions and Ideas" section

PasteSpecial

The "Pasting" code above copies the contents of the cells (value or formula) AND THE FORMAT. If you want to paste only the value or only the formula you will use:
Range("A1:A5").Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Application.CutCopyMode=False

Don't write the paste special code use the macro recorder and copy/paste the result within your code. You can change manually the xlPasteFormulas for xlPasteValues. Personally every time I start a new project I use the macro recorder at the beginning to create this line of code and I then use it all around my project.

In the code above you will notice the second argument "Operation:=xlNone" this argument can take 5 different values including "Operation:=xlAdd" and "Operation:=xlMultiply". Try this in Excel:

Enter numbers in cells A1 to A5. Enter a value in cell B1 (3 for example) then select cell B1, copy, select cells A1 to A5 and then go to Edit/Paste/Special/Add". You notice that 3 has been added to each cells A1 to A5.

You can use this approach to add, subtract, divide and multiply. It is sometimes faster than using a formula that you copy/paste. It is particularly interesting when you import data from an old legacy system where the numbers are text and with which you cannot execute any calculation. You enter 1 in an empty cell and you Copy/PasteSpecial/Multiply and the numbers become real numbers or the date become dates.

Using the Macro Recorder

Use the Macro Recorder for everything pertaining to format and colors of cells

With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Selection.NumberFormat= "@"

Error Messages

You will often see the following error message when you code for Cells, Range, Columns and Rows:

It is because you have forgotten a parenthesis or a quotation mark.

You will also see the following error message when you code for Cells, Ranges, Columns and Rows:

when you forget the period.

Discover more tips
and practical examples of real solutions to real problems
with step by step development instructions in the 25

Resource Spreadsheets