Copy Data From One Cell to Another in Excel Automatically

This tutorial demonstrates how to copy data from one cell to another automatically in Excel.

 

Copy Data Automatically

To copy and paste data from one cell to another in your current worksheet, you can create a VBA macro.

1. In the Ribbon, go to Developer > Visual Basic. If you don’t have this tab available, find out how to add the Developer tab.

 

 

2. In the VBA window, in the Menu, select Insert > Module.

 

 

3. In the code window on the right side, type the following macro:

Sub CopyData()
   Range("B2").Copy Range("D2")
End Sub

This copies the data that is in cell B2 to cell D2.

 

 

4. You can extend this macro to copy to more than one cell.

Sub CopyData()
   Range("B2").Copy Range("D2:D6")
End Sub

This copies the data in cell B3 across to D2 and down to D6.

 

 

ActiveCell

In the two macros above, you do not have to have cell B2 selected in order to copy the data as the range is specified in the macro. If, however, the macro uses the ActiveCell property, then you would need to select the cell with data you want to copy before running the macro.

Sub CopyData()
   ActiveCell.Copy Range("D2")
End Sub

Note: You can also use VBA code for many other copy and paste options in Excel.

Create a Formula in VBA to Copy Data

You can also copy data automatically in Excel using a formula. You can create the formula manually, or use VBA.

Sub CreateFormula()
   Range("D2") = "=B2"
End Sub

Or use a macro to copy a cell to a range of multiple cells. However, due to the nature of Excel – that it copies formulas relative to each cell address – make sure you anchor the cell in place using absolute addressing.

Sub CreateFormula()
   Range("D2:D6") = "=$B$2"
End Sub

The formula would then be copied down from cell D2 to D6.