Copy and Paste Values With VBA

In this lesson, you find multiple examples of how to copy and paste values with VBA.

Copy row or column between sheets with numbers

The following subroutine copies the first column (A) in the first sheet (“Sheet1”) and pastes it into the first column of the second sheet (“Sheet 2”).

And the second example. This time let’s copy the first row.

Copy row or column between sheets with names

Often using numbers in selecting rows and column may be very inefficient. That’s why using names is usually a better idea. Of course, it does matter for sheets and columns, and not for rows because they use numbers anyway.

Copy multiple rows or columns between sheets

An easy way to copy multiple adjacent columns is as follows. You have to use Range. This code copies the first three rows: A, B, and C.

Copy Range

Apart from copying rows and columns, you can also copy cell ranges.

This code copies cells B2, B3, C2, and C3.

Use a loop to copy (offset)

The following code copies cell from the first three columns and pastes them in B, C, and E (Offset = 1).

Copy without format (paste special)

So far we have been pasting data with the exact formatting. But sometimes you don’t want to keep the formatting. In order to change this code, we have to do a simple modification.

Copy and paste with transpose

In order to transpose the copied values set transpose to true.