Microsoft KB Archive/68642

{|
 * width="100%"|

Macro to Transfer an Array Without Selecting the Data

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

-

SUMMARY
The following is an illustration of a fast way to copy an array from one reference to another without using the Edit Copy command, selecting the new area, and using the Edit Paste command. This method may also be used in the place of multiple FORMULA statements to transfer several cells of information to another reference. Please keep in mind that the FORMULA.ARRAY statement must be entered with CTRL+SHIFT+ENTER (as a result, it is enclosed in French braces, which are not entered by the user).

  A1: Array_Transfer A2: {=FORMULA.ARRAY(A5:A9,OFFSET(ACTIVE.CELL,0,0,5,1))} A3: =RETURN A4: A5: 1 A6: 2 A7: 3 A8: 4 A9: 5

MORE INFORMATION
The above macro will copy the contents of cells A5:A9 on the macro sheet to the active sheet, starting with the active cell. The general form of the statement in cell A2 is

  {=FORMULA.ARRAY(array,OFFSET(ACTIVE.CELL,0,0,# of rows in   array,# of columns in array))}

where &quot;array&quot; need not be on the macro sheet. It may be a remote reference to another sheet, but that sheet must be open.