This tutorial demonstrates how to copy data from one cell to another automatically in Excel.
Copy Data Automatically
- In the Ribbon, go to Developer > Visual Basic. If you don’t have this tab available, find out how to add the Developer tab.
- In the VBA window, in the Menu, select Insert > Module.
- 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.
- 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.
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
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.