Copy Worksheet User Guide

1. Contents

• 1. Contents
• 2. Overview
• 3. Menu
• 4. Run
• 5. Edit
  • 5.1. Edit Select Source Worksheets
  • 5.2. Edit Select Target Worksheet
  • 5.3. Edit Source to Target Copy Rules
    • 5.3.1. Edit Source Copy Rules
    • 5.3.2. Edit Source Conditions
    • 5.3.3. Edit Target Copy Rules
    • 5.3.4. Edit Target Conditions
  • 5.4. Edit Rule Set Name
  • 5.5. Edit Buttons
• 6. Delete

2. Overview

Copy Worksheet is a tool for automating the process of conditionally copying rows and columns from one or more source worksheet to a single target worksheet.

Copy Worksheet allows the users to define multiple sets of rules that are saved and later executed against a set of source worksheets to a target worksheet. The rules can be as simple as copy column A from source Sheet 1 to column B in target Sheet 2 or complex in having conditional statement for both source sheet and target sheet, e.g. copy column A from source Sheet 1 if column D in source Sheet 1 is greater than 1000 paste to column B target Sheet 2 where column E equals column C in source Sheet 1.

The following sections describe he main functions of the tool and how to use them.

3. Menu

The menu appears under the Worksheet Copy tab and shows three options:
1) Run - allows the user to select a saved set of rules to run
2) Edit - allows the user to edit an existing set of rules or create a new set by selecting <Add WKS="" Copy="">
3) Delete - allows the user to delete a set of rules

4. Run

Run does just that, i.e. it will execute the selected set of rules. There are a number of validation checks carried out prior to running to ensure the Worksheets exist. In summary the following rules are applied for each cell processed from each sheet:
1) If the specified source sheet cell condition rules are met then
2) Paste the source cell to target cells, the source or target may be in different directions (row to column or column to row)
3) Either conditional paste, based on condition(s) met for the row or column or just paste at specified row or column or first empty row or column. Rules as follows, more details in edit section.
a) If there is a condition for the target sheet, then match the condition rules in target and paste to that row / column. Note if more than one can match then the data will be copied to all.
b) Else if
i) If check specific row or column empty paste to the first empty row or column after the target start row or column cells
ii) Else if check empty selected paste to the first empty row or column after the target start row or column
iii) else paste to the target start row or column

5. Edit

The Worksheet Copy Edit screen allows the user to:
1) Add a new set of rules
2) Edit an existing set of rules
3) Clone a set of rules to a new set of rules
4) Validate a set of rules
The screen can be split into a number of areas, see Figure 2 - Edit Worksheet Copy:
1) Add, modify, delete source worksheets
2) Add or modify a target worksheet
3) Add, modify, delete rules for copying data from a source to a target
4) Naming the rule set
5) Action buttons
These area will be described in more detail in the following sections.

5.1. Edit Select Source Worksheets

You can select one or more Source Worksheets. Each Source requires a name to be specified, this is used when defining the rules, see 5.3 Edit Source to Target Copy Rules.
1) To add a new source sheet, select the Source Workbook as in Figure 3 - Edit Select Source Workbook, then select the Source Worksheet from the dropdown list. Name the Source then select to add the Source Worksheet.
2) To update an existing entry, select from list edit as in 1) above and then select to save the amendment.
3) To delete an existing entry, select from list and select to delete the entry.

5.2. Edit Select Target Worksheet

Similar as with the selecting a Source Worksheet, the user selects a target worksheet by selecting a workbook then a worksheet from the drop down list. There is only a single target worksheet per set of rules therefore there is no need to give each a name.

5.3. Edit Source to Target Copy Rules

As the name suggests Copy Rules are is where the user specifies the rules for copying cells from a Source Worksheet to a Target Worksheet. The user can define multiple rules per Source Worksheet and multiple Source Worksheets with rules. For each rule entry there are rules applied to the Source Worksheet to determine which cells to copy and rules applied to the target as to how and where the cells that meet the Source Worksheet rules should be pasted. The following sub-sections describe the two.

5.3.1. Edit Source Copy Rules

There are a number of data fields that are required to determine which Source Worksheet cells to copy. Each data field is describe below:
1) Name - (Mandatory) each rule entry is given a name. This can be anything, but meaningful to the set of Source Worksheet cells the Copy Rule applies to. The name should be unique within the rule set.
2) Source Name - (Mandatory) selected from a drop down list this is the name you gave the Source Worksheet as described in section 5.1. Edit Select Source Worksheets
3) Source Range - (Mandatory) selected from the Source Worksheet itself, the ellipse button opens the Source Worksheet and allows you to select cells on the first row or column to start the copy from. The Cells:
a) have be from on row or one column.
b) can be a range or individual disconnected set of cells e.g. B2-E2, G2, L2
c) order in which they are selected in the Source Worksheet range are pasted to the corresponding cell position in the Target Worksheet range.
4) Repeat - (Mandatory) either 'Used Range' or 'Count' can be selected, if the latter the count must be entered in the Value data field. The 'Used Range' will repeat the copy of rows or columns till the last used cells in the range specified in Source Range.
5) Value - (Conditional) if Repeat is 'Count' then the number of rows or columns to repeat the copy for must be specified in this data field.
6) Conditions - (Optional) the source Conditions, filters each row or column as to whether it should be copied or not. See 5.3.2 Edit Source Conditions

5.3.2. Edit Source Conditions

The conditions dialogue allows the user to filter the rows or columns to be copied. The user can select a Source Cell from the copy row or column and ensure it meets the condition specified in Source Cell/Value. The later data field can point to a cell on the Source Worksheet or the user can add a value to the data field. The condition operators can be one of:
• Equals
• Does Not Equal
• Is Greater Than
• Is Greater Than or Equal To
• Is Less Than
• Is Less Than or Equal To
• Begins With
• Does Not Begin With
• Ends With
• Does Not End With
• Contains
• Does Not Contain
The user can combine conditions together using the 'And' and 'Or' operators e.g. "( R2C2 Equals R1C1 And R2C3 Is Greater Than 1000) or (R2C5 Equals yes)"
The available cells are high lighted in a red dotted line when selecting target and source cells, as shown in Figure 5 - Edit Source Conditions.
The buttons allow the user to update, add and delete conditions respectively for changes to be applied.
Once conditions have been specified the user needs to press Ok to save changes or Cancel to exit without saving changes.

5.3.3. Edit Target Copy Rules

The Target Copy Rule determine where and under what conditions the user wishes to paste the copied source rows or columns. Here are the details of each data field:
1) Target Range - (Mandatory) selected from the Target Worksheet itself, the ellipse button opens the Target Worksheet and allows you to select cells on the first row or column in the paste range. The Cells:
a) have be from on row or one column.
b) can be a range or individual disconnected set of cells e.g. B2-E2, G2, L2
c) order in which they are selected in the Target Worksheet range are mapped to the corresponding cell position in the Source Worksheet range. The number of cells in the Target Range must equal the number of cells in the Source Range.
d) the target row or column does not need to be the same as the source e.g. you can transpose a source row to a target column.
2) Conditions - (Optional) the target Conditions, filters each source row or column as to where it should be pasted if at all. See 5.3.4 Edit Target Conditions
or
3) Is Empty Check Yes - (Optional) makes sure we are pasting to the first empty row or column in the target range. This stop overwriting data, especially when you have multiple source copy rules which are unknown in length.
4) Is Empty Check RC - (Optional) allows the user to select a specific row or column to check that is empty rather than the rows or columns in thr range. An eample of this use is when column one has the value not in the copy range but you want the past to start after the last value in column one.

5.3.4. Edit Target Conditions

In the Target Conditions a Target Cell is compared to a Source Cell using one of the operators. Only if the conditions are met is the source row or column pasted to the target row or column. The condition operators can be one of:
• Equals
• Does Not Equal
• Is Greater Than
• Is Greater Than or Equal To
• Is Less Than
• Is Less Than or Equal To
• Begins With
• Does Not Begin With
• Ends With
• Does Not End With
• Contains
• Does Not Contain
The user can combine conditions together using the 'And' and 'Or' operators e.g. "( R2C2 Equals R1C1 And R2C3 Is Greater Than 1000) or (R2C5 Equals yes)"
The available cells are high lighted in a red dotted line when selecting target and source cells, as shown in Figure 6 - Edit Target Conditions.
The buttons allow the user to update, add, and delete conditions respectively for changes to be applied.
Once conditions have been specified the user needs to press Ok to save changes or Cancel to exit without saving changes.

5.4. Edit Rule Set Name

Rule set Name (Mandatory) is used to identifiy the set of rules on the Worksheet Copy Menu. It is the one data field that must always be entered before saving the rule set.

5.5. Edit Buttons

There are four Edit Buttons:
1) Clone Settings - creates an exact copy of the Rule Set with a blank rule set Name. This allows the user to quickly create a new set of rules very similar to an existing set, e.g. where the rules are the same but there is a different Targe Worksheet.
2) Validate - performs a series of checks to ensure that all the data fields have valid data in them. This is useful to run before saving although you can save the rule set with invalid data fields if the user needs to.
3) Save Settings - saves the data fields in the rule set is saved to the Workbook. Note there is no validation of the data done while saving.
Note: Although the rule set is saved to the Workbook, the user still needs to save the Workbook to ensure the rule set is fully saved to disk.
4) Close - closes the Worksheet Copy edit dialogue. any unsaved changes will be lost.

6. Delete

The Delete menu option alows the user delete a rule set completely. The user is promted to confirm before it is completely removed.