Copying cells and ranges as well as cutting (moving) them is probably the most used group of commands in Excel. This page will cover the basics and more advanced commands and advance to to set the stage for Copy, Cut and Paste without VBA and then jump into VBA commands which needs the foundation of the basics. To skip the basics and jump the VBA click Here (or scroll down).

The very basics of the Copy command:

There are several ways to copy. Only two will be discussed here. First select (highlight) the cells to copy, then:

Press Ctrl+C  or

Click the Copy icon (upper left of the ribbon-looks like a page)

Note at the bottom left of the screen:

Select destination and press ENTER, or press Ctrl+V or click the Paste icon.  If several cells are selected for Copy, selecting only one cell for Paste is needed, usually the upper left of the range copied.

Pressing ENTER performs the Paste function and clears the clipboard. Clicking Paste performs the the Paste function and the contents remain in the clipboard.

When Copy is activated, the data is placed in the Windows clipboard. It remains there until released. When you click Paste, the data is Pasted to the destination cell(s) but remains in the clipboard and can be Pasted again. Pressing ENTER the data is Pasted to the cell(s) and is released from the clipboard.

What is the clipboard? It’s area of the computer memory that hold the data when Copy is activated and is available to be later Pasted.  The clipboard can actually hold more than one set of copied data and can be accessed with the option of selecting any of the recent few items added to the clipboard. This is just a basic discussion. The specifics of the clipboard are beyond the scope of this page.

Going forward, this page will simply say, COPY and/or PASTE without stating the specific steps in doing so.

The basics of the Cut Command:

Think of the Cut command as being the same as the the Copy command with one big difference. When Pasted the original range is deleted.  You may also think of the Cut and Paste command as Moving the data.

Press Ctrl+V  or

Click the Cut icon (upper left of the ribbon-looks like sissors)

Note at the bottom left of the screen:

Select destination and press ENTER, or press Ctrl+V or click the Paste icon. 
If several cells are selected , to Cut selecting only one cell for Paste is needed, usually the upper left of the range moved.

Some differences in Copy and Cut:

When performing Copy and Paste any formulas are pasted Relative and when performing Cut and Paste the formulas are pasted Absolute. If you are new to Excel, special attention with this difference is required.

Relative and Absolute Cell Referencing

When Copying cells that contain formulas, the reference to the source cells will change relative to the original location. Most always this is what you want. See xxxx that goes into this in more detail. If you want the reference to stay with the source cell, copying with absolute cell referencing, then surround the reference with dollar signs ($). This can be done using the F4 key. xxx

When Moving cells the formula reference remains absolute, i.e. the referencing does not change when pasted.

Copy with VBA

There are many VBA commands and structures for Copy, Cut and Paste. What follows are several commands with only brief explanations. These examples are show you code to accomplish your objective, not necessarily to teach you VBA. See other pages in this website for such.

Copy a single cell, A1 to cell A2

Sub Paste_SingleCell()

    Range("A1").Copy Range("A2")

End Sub

The Code will copy the cell A1 and will paste it in cell A2. Think of it as copying From A1 To A2. Notice that the macro performs the task without moving the cell cursor.

Type the following code in the Module(Code) window,

Sub Copy_Range()

Range("B5:C7").Copy Range("F5:G7")

End Sub

The code will copy cell range B5:C7 and will paste it in cell range F5:G7. Again think of it as From and To. While this is technically correct, what’s wrong with this macro? It presumes that you know the size of the range to be copied in the paste section of the macro.  A better macro would be:

Sub Copy_Range()

   Range("B5:C7").Copy Range("F5")

End Sub

The paste cell F5 is simply the upper left corner of the Paste range and the entire range B5:C7 will be reproduced at the F5 starting point, no worries about the size of the paste range. Again, the macro works without moving the cell cursor.

Different methods with the same result:

Sub Copy_Range()

  Range("A1").Copy Desination:Range("B3")
  
End Sub

Sub Copy_Range()

  Range("A1").Copy
  Range("B3").Select
  ActiveSheet.Paste

End Sub

Note that this macro acts more like your work in the spreadsheet. The cell cursor is place in cell B3 and then the Paste is performed.

Another approach:

Range("A1").Value = Range("B3").Value

This is technically not a Copy and Paste method.  It says whatever the value is there in cell A1 should be equal to the value in cell B3. Note also the logic is reversed, the from range is on the right and the to range is on the left.  It can be expanded to copy between sheets or workbooks as noted below. With large amounts of data or being copied or several lines of this code in a macro, it tends run much faster that the regular macro methods of copy and paste.

The above examples was copying and pasting on the same worksheet. Now let’s copy and paste from one worksheet to another (in the same workbook):

Sub Copy_AnotherSheet()

Worksheets("Sales").Range("B4:D10").Copy            Worksheets("Budget").Range("B4")

End Sub

The code will copy the cells B4:D10 from the Sales worksheet and paste it  it to B4 in the Budget sheet.  The first line is the From range and second line is the To range.

The above example was copying and pasting from worksheet to another, in the same workbook (file). Now let’s copy and paste from one workbook to another:

Sub Copy_AnotherWorkbook()
    
Workbooks("Dept 1.xlsx").Worksheets("Sales").Range("A1:D10").Copy _
Workbooks("Dept 2.xlsx").Worksheets("Sales").Range("A1")

End Sub

The code will copy the data from the current workbook  to the Paste workbook. In this example, the _ is a continuation character as the code is technically all on the same line.

You can include the Workbook and/or Worksheet names in a macro copying within the same worksheet if desired. But this has a tendency to add clutter and make thing harder to read later.

To copy the entire used range in a sheet:

Sub Copy_Big

Worksheets("Sheet1").UsedRange.Copy Destination:=Worksheets("Sheet2").Range("A1")

End Sub

What is the used range? This is everything from cell A1 to the lower right of the used cells in a range.  To see the used range press End Home. The cursor will jump to the end of the used range.  Make sure you understand the used range as there may be more copied than desired with unexpected results.  xxxx

In Summary

In summary, if the same workbook and sheet, only cell references are needed. If a different worksheet in the same workbook, then the worksheets must be in the code. You guessed it, if in a different workbook (file), then the code must include the workbook and the worksheet.

Defined Names

Defined names can used rather than cell references:

Sub Copy_Range()

   Range("January").Copy Range("February")

End Sub

See xxx for more on Defined Names

More to come, please check back.

Author: Jim Colville

Date: Several articles and exercises since 1997
Summarized and combined into this page: 6/4/2023