Day 10 VBA Hero – With Statements

When writing code sometimes, you need to use the same reference multiple times.

You can repeat the same stuff every line like:

Sub CopyPaste()

Sheets("Sheet1").Range("C3").Copy Sheets("Sheet2").Range("H3").PasteSpecial
Sheets("Sheet1").Range("C5").Copy Sheets("Sheet2").Range("H5").PasteSpecial
Sheets("Sheet1").Range("C7").Copy Sheets("Sheet2").Range("H7").PasteSpecial
Sheets("Sheet1").Range("C9").Copy Sheets("Sheet2").Range("H9").PasteSpecial
Sheets("Sheet1").Range("C11").Copy Sheets("Sheet2").Range("H11").PasteSpecial

End Sub

Or, alternatively, you can use the With statement.

The With statement is used to group lines of code that have an identical structure.

Using the above code as an example, there are two things that repeat in the example above.

The Sheets(“Sheet1”) reference and the Sheets(“Sheet2”) reference.

You can only choose one to do with the With statement.

Here are both examples that you could use with the above example of the With Statement.

Sub CopyPasteWithExample1()

With Sheets("Sheet1")
.Range("C3").Copy Sheets("Sheet2").Range("H3").PasteSpecial
.Range("C5").Copy Sheets("Sheet2").Range("H5").PasteSpecial
.Range("C7").Copy Sheets("Sheet2").Range("H7").PasteSpecial
.Range("C9").Copy Sheets("Sheet2").Range("H9").PasteSpecial
.Range("C11").Copy Sheets("Sheet2").Range("H11").PasteSpecial
End With

End Sub

Please notice that you need to end the With statement when you are done with it. Else, you will receive an error.

Here is the second example.

Sub CopyPasteWithExample2()

With Sheets("Sheet2")
Sheets("Sheet1").Range("C3").Copy .Range("H3").PasteSpecial
Sheets("Sheet1").Range("C5").Copy .Range("H5").PasteSpecial
Sheets("Sheet1").Range("C7").Copy .Range("H7").PasteSpecial
Sheets("Sheet1").Range("C9").Copy .Range("H9").PasteSpecial
Sheets("Sheet1").Range("C11").Copy .Range("H11").PasteSpecial
End With

End Sub

The indenting I have added is of course to increase readability.

Today’s homework:

Write some code that does the following

Copy and paste from one sheet to another. Different cells reference to what I have done.

Ensure to have one value in a cell that has the word “Excel”.

Check the value of each cell before copying and pasting by using and IF statement.

If the value is “Excel”, have a pop-up box that tells you that the value “Excel” has been identified.

Do not copy that cell.