# 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:
`='Sample Dataset'!B5`
• 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:
`='Sample Dataset'!C5`
• 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:
`='Sample Dataset'!D5`
• 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:
`=C14`

• 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:
`=D14`
• 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:

Note:

The code is written to copy cells C14:D16 and paste them into cells F6:G8. Change the codeâ€™s cell references as required.

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

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF