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”).

Sub CopyCol()
   Sheets(1).Columns(1).Copy
   Sheets(2).Columns(1).PasteSpecial xlAll
End Sub

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

Sub CopyRow()
   Sheets(1).Rows(1).Copy
   Sheets(2).Rows(1).PasteSpecial xlAll
End Sub

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.

Sub CopyCol()
   Sheets("Sheet1").Columns("A").Copy
   Sheets("Sheet2").Columns("A").PasteSpecial xlAll
End Sub

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.

Sub CopyColMultiple ()
   Sheets("Sheet1").Range("A:C").Copy
   Sheets("Sheet2").Range("A:C").PasteSpecial xlAll
End Sub

Copy range

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

Sub CopyColRange()
   Sheets("Sheet1").Columns.Range("B2:C3").Copy
   Sheets("Sheet2").Columns.Range("B2:C3").PasteSpecial xlAll
End Sub

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

Use a loop to copy (offset)

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

Sub CopyColOffset()
Offset = 1
   For i = 1 To 3
      Sheets("Sheet1").Columns(i).Copy
      Sheets("Sheet2").Columns(i + Offset).PasteSpecial xlAll
   Next i
End Sub

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 paste with transpose

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

Sub CopyColTraspose()
   Sheets("Sheet1").Range("A1:C3").Copy
   Sheets("Sheet2").Range("A1").PasteSpecial Transpose:=True
End Sub

Posted in vba