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.
To copy from a range in another sheet, Sheet4 in this case, to the active cell you need to change the code to:
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.
Application.Goto Reference:="Engineering" OR Range("Engineering").Select
Assigning range names to a range of cells.
Selection.Name = "myRange"