Macro to Transfer an Array Without Selecting the Data

 Last reviewed: July 7, 1997
Article ID: Q68642

The information in this article applies to:
  • Microsoft Excel for Windows, version 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: 5.00 2.0 2.00 2.01 2.1 2.10 3.0 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 7, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.