30 Advanced Excel Tips for 2026

Excel Champs / VBA / VBA Copy Range to Another Sheet + Workbook
By Puneet Gogia (Microsoft MVP)

VBA Copy Range to Another Sheet + Workbook

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Worksheets("Data").Range("A1:D20").Copy Destination:=Worksheets("Report").Range("A1") copies the range, with its values, formulas and formatting, to another sheet. Give only the top-left cell of the destination; Excel fills the rest to match the size of the copied range.

To copy values only, assign them directly without the clipboard: Worksheets("Report").Range("A1:D20").Value = Worksheets("Data").Range("A1:D20").Value. Both ranges must be the same size.

To copy to a new sheet, add it first and use the sheet it returns:

Dim ws As Worksheet
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = "Report"
Worksheets("Data").Range("A1:D20").Copy Destination:=ws.Range("A1")

To copy into another workbook, open it and refer to it by its full file name, including the extension: Workbooks("Sales.xlsx").Worksheets("Sheet1").Range("A1"). Without the extension, the reference can fail with “Subscript out of range”. To add the data below what’s already there, use Destination:=ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0).

To copy a cell or a range of cells to another worksheet, you need to use VBA’s “Copy” method. In this method, you need to define the range or the cell using the range object that you wish to copy and then define another worksheet along with the range where you want to paste it.

Copy a Cell or Range to Another Worksheet

Range("A1").Copy Worksheets("Sheet2").Range("A1")
  1. First, define the range or the cell that you want to copy.
    define the range or cell
  2. Next, type a dot (.) and select the copy method from the list of properties and methods.
    type a dot and select the copy method
  3. Here you’ll get IntelliSense to define the destination of the copied cell.
    define the destination of the copied cell
  4. From here, you need to define the worksheet and then the destination range.
    define the worksheet and than destination range

Now when you run this code, it will copy cell A1 from the active sheet to “Sheet2”. There’s one thing that you need to take care of: when you copy a cell and paste it to a destination, it also pastes the formatting there.

But if you simply want to copy the value from a cell and paste it into a different worksheet, consider the following code.

Worksheets("Sheet2").Range("A1") = Range("A1").Value

This method doesn’t use the Copy method but simply adds the value to the destination worksheet using an equal sign and the Value property of the source cell.

Copy Cell from a Different Worksheet

Now let’s say you want to copy a cell from a worksheet that is not active at the time. In this case, you need to define the worksheet with the source cell, just like in the following code.

Worksheets("sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")

Copy a Range of Cells

Range("A1:A10").Copy Worksheets("Sheet2").Range("A1:A10")
Range("A1:A10").Copy Worksheets("Sheet2").Range("A1")

Both lines do the same thing. In the first line, the destination is the same size as the range you copy, and in the second line, it’s only the top-left cell. VBA uses that cell as the starting point and pastes the whole range from there.

And to copy only the values of a range, set the Value of the destination range equal to the source range. Make sure both ranges are the same size:

Sub copy_range_values()
Worksheets("Sheet2").Range("A1:A10").Value = Worksheets("Sheet1").Range("A1:A10").Value
End Sub

Paste Only Values or Formats with PasteSpecial

If you want to choose what gets pasted, copy the range first and then use the PasteSpecial method on the destination. Use xlPasteValues for values only, xlPasteFormats for formatting only, or xlPasteFormulas for formulas. In the end, clear the copy mode to remove the moving border around the copied range.

Sub paste_values_only()
Worksheets("Sheet1").Range("A1:A10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Copy a Cell to a New Worksheet

To copy cells to a brand-new worksheet, add the worksheet first, store it in a variable, and then use that variable as the destination. The following code adds a new sheet at the end and copies A1:A10 from Sheet1 to it.

Sub copy_to_new_sheet()
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
Worksheets("Sheet1").Range("A1:A10").Copy newSheet.Range("A1")
End Sub

Copy to the Next Empty Row of Another Worksheet

When you add data to a list on another sheet, you need to find the last row first and then paste one row below it:

Sub copy_to_next_row()
Dim nextRow As Long
With Worksheets("Sheet2")
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Worksheets("Sheet1").Range("A1:C1").Copy .Range("A" & nextRow)
End With
End Sub

Move a Cell to Another Worksheet

If you want to move the cell instead of copying it, use the Cut method in the same way. It removes the value from the source cell and pastes it into the destination:

Sub move_cell()
Worksheets("Sheet1").Range("A1").Cut Worksheets("Sheet2").Range("A1")
End Sub

Copy a Cell to a Worksheet in Another Workbook

When workbooks are open but not saved yet.

Workbooks("Book1").Worksheets("Sheet1").Range("A1").Copy _
Workbooks("Book2").Worksheets("Sheet1").Range("A1")

When workbooks are open and saved.

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _
Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")

Copy a Cell to a Worksheet in Another Workbook which is Closed

'to open the workbook that is saved in a folder on your system _
change the path according to the location you have in your _
system
Workbooks.Open "C:\Users\Dell\Desktop\myFile.xlsx"

'copies cell from the book1 workbook and copy and paste _
it to the workbook myFile
Workbooks("Book1").Worksheets("Sheet1").Range("A1").Copy _
Workbooks("myFile.xlsx").Worksheets("Sheet1").Range("A1")

'close the workbook and after saving
Workbooks("myFile.xlsx").Close SaveChanges:=True

Related: How to Open a Workbook using VBA in Excel

Frequently Asked Questions

Why do I get “Subscript out of range” when copying to another sheet?

The sheet or workbook name in the code doesn’t match an open sheet or workbook. Check the spelling and any spaces in the name, and for a saved workbook, include the file extension, like “Book2.xlsx”.

How do I copy a cell without formatting?

Set the Value of the destination equal to the Value of the source, or use PasteSpecial with xlPasteValues, as shown above.

Do I need to activate the other sheet before copying?

No. When you refer to the sheet in the code, like Worksheets(“Sheet2”).Range(“A1”), VBA copies and pastes without selecting or activating anything, which also makes the code faster.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel