Sunday, January 24, 2010

How do I copy a range with an Excel macro? (And over Range macros)

To copy data from a specific range can be done with the following macro in Microsoft Excel 2007. In this example, data is copied from the current sheet to the active cell.

Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub

To copy from a range in another sheet, Sheet4 in this case, to the active cell you need to change the code to:

Sheets("sheet4").Range("A1:A3").Copy Destination:=ActiveCell

To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Engineering". You may also use an alternative method like the Range select method. Naming a range in Excel is recommended rather than specifying an absolute cell reference.

Sub GoHere()
Application.Goto Reference:="Engineering" OR Range("Engineering").Select
End Sub

Assigning range names to a range of cells.

Sub RngName()
Selection.Name = "myRange"
End Sub