Macro to Transfer an Array Without Selecting the Data

ID: Q68642

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 "array" need not be on the macro sheet. It may be a remote reference to another sheet, but that sheet must be open.

REFERENCES

"Function Reference," version 4.0, pages 6, 169, 299
"Microsoft Excel Function Reference," version 3.00, pages 3, 90, 163
"Microsoft Excel for Windows Functions and Macros," version 2.x, page 286

Additional query words: 2.0 2.00 2.01 2.1 2.10 3.0 4.0

Keywords :
Version :
Platform :
Issue type :