If you spend a lot of time copying and pasting data from on Excel sheet to another you might want to think about automating the process. Automating task in Excel can be easily done by using a macro or with some VBA code. For this tutorial we will show you how to copy cell value in VBA in Excel 2010.
You will need a copy of Microsoft Office 2010 that has Excel in it. So make sure you do before you proceed.
Copy Cell Value in VBA in Excel
Below I have an Excel workbook that has a few data entered into some cells in column A. You can use a spreadsheet that has data already or just mimic what I have below.
After you have your workbook all setup we need to go to the Developer tab on the Ribbon. If you do not have the Developer tab on the Ribbon here is how to setup the Developer tab. After you the Developer tab setup, go to it in the Ribbon. Over to the left will be an icon called Visual Basic. Click the icon to launch the Microsoft Visual Basic for Applications window.
You should see the Microsoft Visual Basic for Applications window open. From the Insert menu click on Module to insert a new Module into your workbook.
After inserting the new Module into your workbook you should see a new folder under the Project menu. Locate the folder and double click on the new Module.
The Module will open to the right with a blank page. This is where the fun starts. Since we want to copy the data in cell A1 and paste it in cell B1 we will need to type in the following code:
After you enter the code your Module should look like this.
After entering all the code go to the green play button under the Debug menu and click it. This will run your new code.
After clicking the play button you should see the results on the active spreadsheet. As you can tell from the picture below, cell A1 was copied to cell B1.
Copy Cell Value with a Macro in Excel
Besides copying cell value with VBA code you can use a Macro to record you copying a cell and pasting cell values. To do this go to the Developer tab again and click on Record Marco.
The Record Macro window will open. Here you can give your Macro a name, shortcut key, specify what workbook you want to store it in and also a description for it. When you are done filling out the information click OK to continue.
Your Macro will automatically start recording all your actions in Excel. Go over to the cell you want to copy and right click on it. Select Copy form the right click menu.
Next head over to the cell you want to paste the copied data to. Right click on the cell and chose Paste from the right click menu.
After you are finished with the copying and pasting, click the blue stop button at the bottom of the Excel application.
Now that your Macro is all recorded lets go back to the Visual Basic application by clicking Visual Basic under the Developer tab. When Visual Basic opens you will notice a new Module under your Project window.
Double click the new module to open it. You will see the code for the Macro you just recorded. You can run the macro by pressing the green play button again.
That is it. You now have two ways to copy cell values in Excel using either a macro or VBA. Later we will show you how to assign VBA code or a Macro to a button so you can run your automation a lot faster.