How to Copy Paste a Range to a VBA Excel Workbook

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. 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. 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 SubChange 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. 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.