You can use the following syntax in VBA to copy a range of cells to another sheet:

Sub CopyRangeToSheet()

Worksheets("Sheet1").Range("A1:C11").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial

Application.CutCopyMode = False

End Sub

This particular macro will copy the cells in the range A1:C11 of Sheet1 and paste them in the range starting in cell A1 of Sheet2.

Note: The line Application.CutCopyMode = False specifies that the cut and copy mode should be turned off after running the macro.

The following example shows how to use this syntax in practice.

Example: Copy Range to Another Sheet Using VBA

Suppose we have the following dataset in a sheet called Sheet1 in Excel that contains information about various basketball players:

Suppose we also have another sheet called Sheet2 that is currently empty:

Suppose we would like to copy all of the values in the range A1:C11 of Sheet1 and paste them starting in cell A1 of Sheet2.

We can create the following macro to do so:

Sub CopyRangeToSheet()

Worksheets("Sheet1").Range("A1:C11").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial

Application.CutCopyMode = False

End Sub

When we run this macro, we receive the following output in Sheet2:

Notice that all of the values from the range A1:C11 from Sheet1 have been copied into Sheet2 starting in cell A1.

By default, the values were pasted with their source formatting.

If you’d like to only paste the values without source formatting, you can specify Paste:=xlPasteValues as follows:

Sub CopyRangeToSheet()

Worksheets("Sheet1").Range("A1:C11").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste=xlPasteValues

Application.CutCopyMode = False

End Sub

When we run this macro, we receive the following output in Sheet2:

Notice that only the values (without formatting) from the range A1:C11 from Sheet1 have been copied into Sheet2.

Note: You can find the complete documentation for the VBA PasteSpecial method here.