AX2296

Setting up copy actions

Using action code processing, you can copy cell formatting and/or content from one location to another within the sheet. The Copy tag is used to perform the copy action, and this tag defines the following:

• The source location to be copied
• The target location for copying
• The scope of the copy action: formulas or values only, formats only, or both

Use of copy actions requires an ActionCodes tag to be set up in the sheet. Within the ActionCodes control column and control row, you can define copy, lock, and unlock actions.

Creating action tag pairs for copy actions

To define a copy action, you create an action tag pair by placing corresponding copy tags in the ActionCodes control row and control column. The intersection of the action tag pair defines an action cell for processing. For example:

Each action tag pair must consist of the following:

• One copy tag that contains the necessary parameters to define the copy action. At minimum, a source location must be specified for the copy action. The other parameters may be omitted to assume default actions.

• One copy tag without parameters (simply [Copy]). The purpose of this tag is to create the action tag intersection that defines the action cell.

In this example, the copy tag that defines the copy action is placed in the ActionCodes control row, and the corresponding "simple" copy tag is placed in the ActionCodes control column. You can reverse the placement of the tags if desired. As long as the action tag pair is valid, it doesn't matter where each tag is placed.

If you want the copy operation to apply when inserting or overwriting calc methods, the calc method itself must be saved with the necessary action tag for the row (in the control column). That way, when the calc method is brought into the sheet, the control column will contain an action tag, and a copy operation can be applied to the calc method.

If an action tag pair is invalid, or if a tag contains invalid syntax, then the action code processing is stopped before any actions are taken and an error message is displayed (or logged, if processing as part of a utility). For example, if an intersection occurs between two copy definition tags (for example, if [Copy;A1] intersects with [Copy;B;2]), then that is an invalid action tag pair.

Copy tag syntax

Copy tags use the following syntax:

[Copy;SourceLocation;TargetSize;PasteType]

The Copy part of the tag identifies it as a copy action and must be included. The other parameters may be defined as follows:

Parameter	Description
SourceLocation	The cell or cells to be copied. The source location can be one of the following: An absolute cell address, such as A1. An offset location, relative to the action cell. By default, the source location is assumed to be a single cell. If you want to copy a range of cells, you can specify an optional size for the source location, in the format WxH (width by height). If used, the size is appended to the source location using a colon. For example: A1:2x2 copies a block of cells that is two columns wide and two rows high, starting at cell A1. The source location must be specified on one (and only one) of the copy tags in an action tag pair. If you want the source location to be the action cell itself, this must be specified as an offset (+0). For more information on defining the source location, including the syntax for specifying an offset location, see the Source location remarks and examples following this table.
TargetSize	Optional. The area to copy into, in the format WxH (width by height), in relation to the action cell. You should omit the target size if you want to assume the copy area based on the specified source size. For example, if the source is one cell, then the contents of that single cell will be copied into the action cell. If the source is two cells wide, those two cells will automatically be copied, starting at the action cell. The target size is used when you want to copy a single source cell into multiple target cells. For example, you could specify a target size of 3x1, and the source cell will be copied into the action cell and the two cells immediately to the right of the action cell (the three-cell range starting with the action cell). The target size always begins with the action cell and then progresses to the right (the width of the area) and down (the height of the area). NOTE: You can specify either a source size or a target size, but not both. Since the target size is assumed automatically if omitted, there is no reason to specify it if a source size is specified.
PasteType	Optional. Specifies what to paste into the target cells: Formats: Copies formats only. Formulas: Copies formulas and number formats. FormulasOnly: Copies formulas only. Values : Copies values only. All : Copies cell contents and formats. If omitted, the default is All.

Source location remarks and examples

When defining the source location for the copy action, you can specify an absolute cell reference or an offset location.

Absolute cell references can be to any cell in the workbook: for example, A1 or Sheet1!A1. You do not have to place dollar signs ($) in the cell reference to make it absolute—A1 is always interpreted as A1, regardless of where in the sheet the action is being performed.

The offset location is relative to the action cell, and can be specified as follows:

• Relative column and relative row: Both the column and row are relative to the action cell. The relative location is specified as follows:

  +C#+R#

  Where C# is the number for the column offset and R# is the number for the row offset.

• Absolute column and relative row: The column is absolute, but the row is relative to the action cell. The location is specified as follows:

  CL+R#

  Where CL is the column letter and R# is the number for the row offset.

When specifying an offset, the number can be positive or negative. Positive numbers are evaluated to the right and down from the action cell, and negative numbers are evaluated to the left and up from the action cell.

The default value for the row offset is zero (meaning, the row containing the action cell) and can be omitted. The column must always be specified, either as an offset or as an absolute column.

The following are examples of valid source locations:

Source Location	Type	Description
A1	Absolute	Copy cell A1 in the current sheet.
A1:3x2	Absolute	Copy a block that is 3 cells wide and 2 rows high, starting at cell A1.
A1:3	Absolute	Copy a block that is 3 cells wide, starting at cell A1.
-12	Relative / Relative	Copy the cell that is 12 columns to the left of the action cell, within the same row as the action cell.
-12+2	Relative / Relative	Copy the cell that is 12 columns to the left of the action cell and 2 rows down from the action cell.
+8-2:6	Relative / Relative	Copy a block that is 6 cells wide, starting at the cell that is 8 columns to the right and 2 rows up from the action cell.
B	Absolute / Relative	Copy the cell in column B, within the same row as the action cell.
B+2	Absolute / Relative	Copy the cell that is in column B, two rows down from the action cell.
B-2:1x2	Absolute / Relative	Copy a block that is 1 cell wide and 2 rows high, starting at the cell that is in column B and two rows up from the action cell.
+0	Relative / Relative	Copy the contents of the action cell. For example, the action cell might contain a formula, the result of which you want to copy and paste as a value.

Copy tag examples

A typical use for copy actions is when performing rolling forecasting. Each month, the plan files would be refreshed to bring in the latest month of actuals. As the actuals are brought in, copy actions are used to change the formatting of the cells from the yellow-background, blue-text format used for input cells, to the white-background, black-text format used for actual values from the database.

For example, the following screenshot shows how the row would appear when the current planning period is period three. (This is not an actual implementation example; it is intended to illustrate the copy concepts.)

The values in Jan-March are actual values from the database, while the remaining months have inputted projection values.

When the current planning period is changed to period 4 and the plan files are refreshed, actuals are brought in for April and the copy operation is extended to affect the column for that month:

Note the following about this example:

• A formula is being used in the copy tag to retrieve the current planning period value and set the target size as appropriate. Previously the formula returned 3, now it returns 4. Therefore the copy operation is applied to the four-cell block starting at the action cell of F8.
• The designated source location of A2 is providing the white-background, black-text format for the copy operation. If the source cell is locked, the target cell will also be locked (when copying formats), so we do not also need a Lock action in this case.
• Only formats are being applied in this case, as specified in the last parameter of the copy tag.
• In this scenario, the copy operation must apply to the full range of months, not just April, so that it can also handle the case of inserting new calc methods. If a new calc method is inserted at this point in time, the copy operation would be applied to months Jan-April, and the user can begin inputting forecasting values for the month of May.

The following are some additional examples of copy tags:

[Copy;A10;Formats]

This example copies the format from A10 into the action cell. Note that when the optional target size parameter is omitted, it does not have to be delimited with an empty semicolon. However, both constructions are valid (the syntax [Copy;A10;;Formats] will also work).

[Copy;+8-2]

This example copies the cell that is 8 columns to the right and two rows up from the action cell. Because the paste type is omitted, it is assumed to be All, which means the contents of the cell will be copied "as is", including formats.

[Copy;B:1x3;Formulas]

This example copies formulas from a three-cell-high block that starts in the cell that is located in column B, in the same row that contains the action cell. These formulas are pasted into a three-cell-high block starting at the action cell. Note that number formats are copied along with the formulas. If you do not want the number formats to be copied, use FormulasOnly instead.

[Copy;B;3;Values]

This example copies values from the cell in column B, in the same row as the action cell. The values are pasted into a three-cell wide block starting with the action cell.

[Copy;+0;Values]

In this example, the action cell itself is the source location (offset of +0). For example, the action cell might contain a formula, and you want to take the result of the formula and paste it back into the action cell as a value.

Performance considerations for copy actions

When possible, it is best to specify a size for either the source or the target to copy a block of cells at once, rather than having several individual copy operations. This minimizes the number of action cells to be processed and can accomplish the same end result with fewer copy operations.

The example in the previous section specified a size for the target. As the current planning period changes, the size increases to cover the necessary number of cells:

You could achieve the same effect without specifying a size. The following example uses formulas in the control row to determine when each individual column should be marked with [Copy]. As the current planning period changes, a new column would become flagged for a copy operation.

While both examples accomplish the same end result, the first example is preferred because it has one action cell to evaluate and one copy action to perform. The format is pasted into all four cells in one operation.

The second example has four action cells to evaluate and four separate copy actions to perform. While the performance difference is likely not noticeable for a single row, when multiplied across many rows in the sheet (and perhaps additional copy or lock operations) the first example should result in faster performance.

TIP: In general, spreadsheet processing in the Windows Client is faster than in the Excel Client. If the processing speed for action codes seems slow in the Excel Client, try using the Windows Client to run the Process Plan Files utility (either manually or via Scheduler) or to refresh individual plan files.