MapWorksheets

def MapWorksheets(resultWorksheet, mapFunction, *worksheets):

MapWorksheets is used internally to translate worksheet formulae into Python code, but you can also call it from user-code.

It takes values from a number of cells in source *worksheets and sets values in cells on the resultWorksheet, by applying a function to all the cells in the source worksheets. If you use 'Edit Worksheet Formula' then this function will be created for you automatically from the formula you enter.

MapWorksheets is useful when the values in the target worksheet can neatly be expressed as the result of applying a formula to the equivalent cells in the source worksheets.

For example, if the cells in 'Sheet1' can be calculated by adding the equivalent cells from 'Sheet2' and 'Sheet3', then we could use the following mapFunction:

def CalculateWorksheet(resultCell, inputCell1, inputCell2):
    resultCell.Value = inputCell1.Value + inputCell2.Value

We can then call MapWorksheets as follows, passing in the target worksheet (Sheet1), the map function and the source worksheets(Sheet2 & Sheet3):

MapWorksheets(workbook["Sheet1"], CalculateWorksheet, workbook["Sheet2"], workbook["Sheet3"])

The CalculateFunction will be called for every populated cell in the first source worksheet. It will be passed in as arguments for the cells in the target worksheet, with the corresponding cells from each of the other source worksheet. In this example, CalculateWorksheet sets the value on the resultCell by adding the values from the two source cells.

Your map function should always take the number of source worksheets plus one (for the destination worksheet), cells as arguments.

If any of the source worksheets have a worksheet level error set on them, then the result worksheet will have an error set on it.

If the resultWorksheet has any values set on it, then they will not be overwritten by a call to MapWorksheets.

If any of the source cells have an error, then that location will be skipped and the formula not applied.

If the call to mapFunction raises an exception for any reason, then that exception is set as an error on the result cell.