When writing VBA for automating report production the ability to copy and paste data from one worksheet to another is a valuable tool.  Learn how to do this with this how-to guide….

 


 

First Steps

The first thing you need to decide is what range of data you wish to copy and from which worksheet, then you must note where you want to place the data and again on which worksheet.

An important part of this process is ensuring that your data is in the same place every time and that worksheet names remain the same, otherwise the VBA script you create initially will not work on future reports.

Walk-through of VBA

In the following example we have a dataset on the worksheet called “ImportSheet” in cells A1 to C14, and we want to move that data into the same position on the worksheet called “DataTable”:

Once we know what we are copying and to where we can open up the VBA Editor and start writing the script.  To open the VBA Editor press ALT and F11, or on the ribbon across the top of the sheet click “Developer” and then “Visual Basic”:

With the VBA Editor open (as above) click on “Insert” then “Module”, this will give you a new module where you can write the VBA Script:

The next stage is to write our VBA Script.  First of all we need to start and name our procedure; we will call it “CopyData”, in the VBA window you would write:

Sub CopyData()

Make sure to include the brackets after the name, then hit the ENTER key:

 

 

VBA Script

Step 1: Make sure the “ImportSheet” is selected

Sheets(“ImportSheet”).Select

Step 2: Copy the range of data we need

Range(“A1:C14”).Copy

Step 3: Select the “DataTable” sheet where we are moving the data

Sheets(“DataTable”).Select

Step 4: Select the cell where you want to begin the paste, in this case we use A1.

Range(“A1”).Select

Step 5: Paste the data

Activesheet.Paste

So putting it all together leaves us with the VBA Script looking like:

Sub CopyData()

Sheets(“ImportSheet”).Select

Range(“A1:C14″).Copy

Sheets(“DataTable”).Select

Range(“A1″).Select

ActiveSheet.Paste

End Sub

And in Excel:

 

If you now run this script (press green play icon or click F5) then you will see your data has been copied to the “DataTable” worksheet:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And it is as easy at that!

For reference you can easily change this from copy and paste to cut and paste by changing the line in Step 2 above to read: Range(“A1:C14″).Cut

This script will usually form part of a bigger piece of VBA code but knowing how to select data and move it using copy or cut and paste is an important concept to learn when starting out with Excel VBA, hopefully this will encourage you to start your quest into VBA programming.

 

Keep Excelling,