We find numerous values, formulas, and calculations when working with Excel. Sometimes, we need to copy and paste only the values. This will be more efficient if we can accomplish this automatically through formulas. In this article, I will show you 2 ways to create formula to copy and paste values in Excel automatically.
Formula to Copy and Paste Values in Excel Automatically: 2 Ways
Say, you have a dataset of 4 students’ names, subjects, and marks. Now, you want to copy these cells’ values and paste them into other cells with a formula. You can follow any of the 2 easy formulas given below to copy and paste values in Excel automatically.
1. Using Cell Reference
i. From Another Sheet
First, say, you need to create an exact sheet like the sample dataset by copying and pasting values from the Sample Dataset sheet in Excel automatically. Follow the steps below to do this.
📌 Steps:
- First and foremost, click on cell B5 and insert the following formula.
='Sample Dataset'!B5- Subsequently, press the Enter key.
- Afterward, place your cursor in the bottom right position of the cell.
- Consequently, a black fill handle will appear.
- Following, drag the fill handle below to copy the same formula dynamically for all the cells below.
- Thus, all the cells of the Sample Dataset sheet’s Name column have been copied and pasted through the formula in the new sheet.
- Now, for the Subject column, click on cell C5 and insert the following formula.
='Sample Dataset'!C5- Subsequently, hit the Enter key.
- Afterward, use the fill handle feature below to fill the Subject column cells.
- Last but not least, click on cell D5 and write the formula in the formula bar.
='Sample Dataset'!D5- Following, hit the Enter key.
- Thus, the D5 cell of the Sample Dataset will be copied and pasted as a value with the formula in Excel automatically in the new sheet.
- Afterward, use the fill handle feature to copy the same formula to copy and paste values in Excel automatically for the Marks column.
Finally, the same dataset is created automatically through the formula to copy and paste values into Excel. And, the final result would look like this.
ii. Inside a Single Sheet
Now, you need to copy a value from one cell and paste it into another cell into a single sheet. For like, you need to showcase the subjects and marks for Adam which are in C14:D14 cells. Follow the steps below to create a formula to copy and paste values in Excel automatically within a single sheet.
📌 Steps:
- First, click on cell F6.
- Following, insert the formula below and press the Enter key.
=C14
- Afterward, use the fill handle feature to copy the formula for all the cells below.
- Similarly, for the Marks column, Click on cell G6 and insert the following formula.
=D14- Subsequently, hit the Enter key.
- Afterward, use the fill handle feature to copy the formula below to fill all the cells.
Thus, you will have your desired result and it looks like this.
Read More: How to Copy Same Value in Multiple Cells in Excel
2. Creating a Formula in VBA to Copy and Paste Values in Excel Automatically
Another way to create a formula to copy and paste values in Excel automatically is to use a VBA formula. Follow the steps below to do this.
📌 Steps:
- At the very beginning, go to the Developer tab >> Visual Basic tool.
- As a result, the VB Editor window will open.
- Subsequently, go to the Insert tab >> Module option.
- As a result, a new module will be created as Module 1.
- Following, double-click on Module 1 and insert the following VBA code inside the editor.
Sub Formula_to_copy_and_paste()
  Range("C14:C16").Copy Range("F6:F8")
  Range("D14:D16").Copy Range("G6:G8")
End Sub- Subsequently, press Ctrl + S on your keyboard.
- As a result, a Microsoft Excel dialogue box will appear.
- Subsequently, click on the No button.
- At this time, the Save As dialogue box will appear.
- Following, choose the Save as type: as .xlsm file and click on the Save button.
- Afterward, close the VB Editor window.
- At this time, go to the Developer tab >> Macros tool.
- As a result, the Macro window will appear.
- Following, choose your created macro from the Macro name: options and click on the Run button.
Thus, you will get your desired result by creating a formula to copy and paste values in Excel automatically. And, the output should look like this.
Note:
The code is written to copy C14:D16 cells and paste them into F6:G8 cells. You can change the code’s cell references as per your need from the VB Editor.
Read More:Â How to Automatically Copy Data from One Cell to Another in Excel
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
So, in this article, I have shown you 2 easy formulas to copy and paste values in Excel automatically. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.
