How to Copy From One Worksheet & Paste to Another Using VBA

Visual Basic for Applications (VBA) is the ideal method to customize Excel 2010 to suit your needs. If you constantly find yourself performing the same tasks, such as copying data in Excel, you can get VBA to perform the task for you. Instead of copying data from one worksheet manually and pasting it into another, write a VBA macro and you can press a button to perform the copy and paste instantly.

Step 1

Click the "Developer" tab and then click "Visual Basic" to open the Visual Basic Editor (VBE) with which you can create VBA.

Step 2

Click "Insert" and then click "Module" to open a blank module window.

Step 3

Cut and paste the following code into the module window:

Sub Copy_Data() Application.ScreenUpdating = False Worksheets("Sheet1").Range("A1:A10").Value = Worksheets("Sheet2").Range("B1:B10").Value Application.ScreenUpdating = True End Sub

Step 4

Tailor the code to your specific needs; as written, the code copies the data in cells A1 to A10 in worksheet 1 to cells B1 to B10 in worksheet 2. For example, if you wanted to copy data from cells C1 to C100 in sheet 4 to cells D1 to D100 in sheet 5, you would rewrite the third line of the code to read:

Worksheets("Sheet4").Range("C1:C100").Value = Worksheets("Sheet5").Range("D1:D100").Value

Step 5

Press "F5" to run the routine.

Tip

Press the "Alt" and "F11" keys together to toggle between the worksheet and your code.

references