In this tutorial, I will show you seven examples of copying a range from one worksheet to another, either within the same workbook or another using VBA. In VBA, you can use the ‘Range.Copy’ method to copy a source range to another sheet, like copying and pasting using the shortcuts CTRL + C and CTRL + V on the Excel user interface.

If you just want to paste values, there is an example below. When using this macro to copy data from one workbook to another, keep these points in mind. You must reference the correct file extension in the Workbooks property (see video above for details). Workbooks do not have to be macro enabled for this to work.

Note: To copy the range to a sheet in another open workbook, you can customize the code by adding a variable that points to the other workbook. You can also use VBA to copy a range of cells and then paste the transposed version of this range into another sheet.

When you need to transfer all data from one worksheet to another,perhaps to create an archive or consolidate monthly reports, you can copy the entire used range in a single step. This method copies every populated cell from the source sheet and pastes it to the top‑left corner of the destination sheet.