How to Copy & Paste a Range to a VBA Excel Workbook

Written by tiesha whatley
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Copy & Paste a Range to a VBA Excel Workbook
Copy and paste a range using VBA in Excel. (typing on a computer image by TEMISTOCLE LUCARELLI from Fotolia.com)

In Excel, a range is a group of selected cells. After the cells have been selected, they can generate related formulas, display a set of blocked information or be copied to another worksheet. You can use VBA (Visual Basic for Application) to copy and paste a range from one Excel workbook to another. It isn't difficult to do once you know the right code. Once you have the code, decide on what module you want to insert the code and what will trigger the code to run.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Microsoft Excel

Show MoreHide

Instructions

  1. 1

    Create two different workbooks in Excel. The first workbook will contain the range that you want to copy while the other one is where you will paste the range into. Keep the workbook that currently has the range open. The other workbook doesn't have to be open to complete the rest of the steps.

  2. 2

    Open the Visual Basic Editor. Click on "Tools > Macros > Visual Basic Editor" in Excel 2003 or earlier. In Excel 2007, you will need to show the "Developer" tab first. Click on the "Office" button and then "Excel Option." Click the checkbox next to "Show Developer tab on the ribbon" under the "General" tab and then click "OK." Click on the "Developer" tab and then click "Visual Basic" to open the editor.

  3. 3

    Click on "This Workbook" in the left-hand pane to open it in the code window. Add the following code to the code window:

    Public Sub CopyRange()

          Workbooks("Book1")._
          Worksheet("CurrentSheet") _
          Range ("A1:C10").Copy_
          Destination:=
          Workbooks("Book2")._
          Worksheets("PasteSheet")._
           Range("A1")
    

    End Sub

    Change the information in the parenthesis to match your workbooks. "Book 1" is the workbook that you are currently working in. "CurrentSheet" is the worksheet that has the range you want to copy. "A1:C10" is the range of cells that you want to copy. "Book2" is the workbook that you want to paste the range. "PasteSheet" is the worksheet that you want to paste the range into. "A1" is the beginning cell that you want to paste the range into.

  4. 4

    Save and run the code. Click on "Tools > Macro > Macros" in Excel 97 to 2003. In Excel 2007, click on the "Macros" button on the "Developer" tab. See the "CopyRange" function in the list of macros. Click on it and then click "Run." The code will run and copy the range from one worksheet and paste it into another.

Tips and warnings

  • You can paste the range in the same workbook or worksheet that you copied it from. Just input the workbook or worksheet name in the code where you would put the destination workbook or worksheet.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.