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.
Things You'll Need
- Microsoft Excel
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.
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.
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()
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.
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 & 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.
- "Excel 2007 Power Programming with VBA"; John Walkenbach; 2007
- Anthony's VBA Page: Excel VBA Basic Tutorial 2
- Photo Credit typing on a computer image by TEMISTOCLE LUCARELLI from Fotolia.com
How to Copy an Array to a Range in Excel 2007
Array formulas are a way for a computer to greatly improve performance in handling data and multiple calculations, and allow Excel to...
Excel VBA Tutorial on Range
Microsoft Excel is a powerful spreadsheet application that allows users to enter and analyze data in a variety of ways. The many...
How to Copy & Paste Macro for Excel
Microsoft Excel macros can replace tedious functions in Excel spreadsheets. For example, instead of typing the same information over and over again,...
How to Transfer Excel VB Data From Range to Array
An array is a data structure that enables you to work with sets of data values in a single structure rather than...
How to Use Variables to Select a Cell in VBA
Visual Basic for Applications (VBA) is a programming language used exclusively for Microsoft Office applications. VBA allows you to customize Excel by...
How to Copy & Paste to Available Rows in Macro
Microsoft Excel is especially fond of contiguous ranges. When a range is broken up by empty cells, Excel adds a layer of...
How to Use ActiveCell in Excel Using a VBA
If you work with Microsoft Office Excel often it's good to know how to use the "ActiveCell" property to add, get, and...