Formula to Copy and Paste Values in Excel Automatically

In this article, we will demonstrate 2 ways to create a formula to copy and paste values in Excel automatically. We’ll use the following dataset of 4 students’ names, subjects and marks to illustrate the methods.

Method 1 – Using Cell Reference

1.1 – Copying to Another Sheet

Let’s replicate our sample dataset in a different sheet by copying and pasting the values from it with a formula.

Steps:

• In cell B5, insert the following formula:

• Press Enter.

• Place the cursor in the bottom right corner of the cell.

A black Fill Handle will appear.

• Drag the Fill Handle down to copy the formula to the cells below.

All the cells of the Sample Dataset’s Name column are copied and pasted by the formula into the new sheet.

• For the Subject column, click on cell C5 and insert the following formula:

• Press Enter.

• Use the Fill Handle to fill the rest of the Subject column’s cells.

• Click on cell D5 and enter the following formula in the formula bar:

• Press Enter.

• Cell D5 of the Sample Dataset is copied and pasted as a value in the new sheet automatically.
• Use the Fill Handle to copy the formula to the rest of the cells in the Marks column.

The final result will look like this:

1.2 – Copying Inside the Same Sheet

Now let’s copy a value from one cell and paste it into another cell in the same sheet. We’ll showcase the subjects and marks for Adam which are in cells C14:D14.

Steps:

• Click on cell F6.
• Insert the formula below and press Enter:

• Use the Fill Handle to copy the formula to the cells below.

• Similarly, for the Marks column, click on cell G6 and insert the following formula:

• Press Enter.

• Use the Fill Handle to copy the formula down to fill all the cells.

The result looks like this:

Method 2 – Creating a Formula in VBA

Another way to create a formula to copy and paste values in Excel automatically is to write one using VBA code.

Steps:

• Go to the Developer tab >> Visual Basic tool.

The VB Editor window will open.

• Go to the Insert tab >> Module option.

A new module will be created called Module 1.

• Double-click on Module 1 and insert the following VBA code in the editor:

Sub Formula_to_copy_and_paste()
  Range("C14:C16").Copy Range("F6:F8")
  Range("D14:D16").Copy Range("G6:G8")
End Sub

• Press Ctrl + S to save the code.

A Microsoft Excel dialog box will appear.

• Click on the No button.

The Save As dialog box will appear.

• Choose the Save as type: as .xlsm file.
• Click on the Save button.

• Close the VB Editor window.
• Go to the Developer tab >> Macros tool.

The Macro window will appear.

• Choose your created macro from the Macro name: list.
• Click on the Run button.

The output should look like this:

Download Practice Workbook

<< Go Back to Copy Cell Value | Formula List | Learn Excel