VB and VBA Users Source Code: Copying data between Excel Ranges
[
Home
|
Contents
|
Search
|
Reply
| Previous |
Next
]
VB/VBA Source Code
Copying data between Excel Ranges
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Monday, January 20, 2003
Hits:
2244
Category:
VBA (Visual Basic for Applications)
Article:
Many developers get stuck on the basics when it comes to VBA. The most common question I get sent is in regards to copying a pasting data between Excel Ranges. If you use trimmed down recorded macro to do this for you, you will most likely be using "Selected" ranges. eg: Range("A1:A10").Select Selection.Copy Range("B1:B10").Select ActiveSheet.Paste This can get fairly labour intensive if you are pasting data between Excel Worbooks or other sheets. The following code demonstrates a fast method of copying and pasting data between various different ranges: Option Explicit 'Purpose : Copies data from one excel range to another 'Inputs : rngSourceData The Excel Range containing the source data ' rngOutput The Excel Range to copy the data to 'Outputs : Returns True on Success 'Author : Andrew Baker 'Date : 13/11/2002 10:14 'Notes : 'Revisions : Function ExcelRangeCopy(rngSourceData As Excel.Range, rngOutput As Excel.Range) As Boolean On Error GoTo ErrFailed rngOutput.Value = rngSourceData.Value ExcelRangeCopy = True Exit Function ErrFailed: Debug.Print Err.Description ExcelRangeCopy = False End Function 'Demonstration routine, showing how to copy data between Excel ranges Sub Test() Dim oWk1 As Workbook, oWk2 As Workbook, lThisRow As Long If Application.SheetsInNewWorkbook = 1 Then 'Set the default number of sheets in a new workbook to 2 Application.SheetsInNewWorkbook = 2 End If 'Create two new workbooks Set oWk1 = Excel.Workbooks.Add Set oWk2 = Excel.Workbooks.Add 'Put some dummy data the first workbook For lThisRow = 1 To 10 oWk1.Sheets(1).Cells(lThisRow, 1).Value = "Cell " & lThisRow Next 'Copy the data into the next column If ExcelRangeCopy(oWk1.Sheets(1).Range("A1:A10"), oWk1.Sheets(1).Range("B1:B9")) Then oWk1.Activate oWk1.Sheets(1).Select MsgBox "Copied range between columns!" Else MsgBox "Failed to copy range between columns!" End If 'Copy the data into the next sheet If ExcelRangeCopy(oWk1.Sheets(1).Range("A1:A10"), oWk1.Sheets(2).Range("A1:A10")) Then oWk1.Activate oWk1.Sheets(2).Select MsgBox "Copied range between sheets!" Else MsgBox "Failed to copy range between sheets!" End If 'Copy the data into the next worbook If ExcelRangeCopy(oWk1.Sheets(1).Range("A1:A10"), oWk2.Sheets(1).Range("A1:A10")) Then oWk2.Activate oWk2.Sheets(1).Select MsgBox "Copied range between workbooks!" Else MsgBox "Failed to copy range between workbooks!" End If End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
