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.
For example, the values in the header row are bold.
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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Paste Values Only with No Formatting
VBA: How to Copy Visible Rows to Another Sheet
VBA: Copy Rows to Another Sheet Based on Criteria
