Macro for Copying/Pasting Equations (CopEqu)

Guidance worksheet

-

The macro copies an equation in a cell of a table selected by the user, and pastes it onto empty cells in the column below the cell selected. This functionality is useful when a table consists of big number of rows, on which to copy and past an equation or other contents.

-

The macro is in an Excel workbook 'ExcEdi1.xls'.

-

The workbook has a worksheet 'Guidance', where there are both instructions and a button for running the macro ('Run CopEqu').

-

'Guidance' worksheet is protected against accidental overwriting and deleting with a password. The password is in cell A14 ('suojaus').

Running the macro

-

The easiest way to run the macro is pressing simultaneously Ctrl + Shift + 'c' or to click 'Run CopEqu' button.

-

The macro asks to decide, where to copy and paste equations. The default is [2:] [in] other workbook than that containing the macro.

-

In picture 2 user decides to copy and paste an equation in a workbook other than the one containing the macro.

-

The browser will be launched in order to let the user to select a workbook for edit. In picture 3 'AscFil.xls' will be selected. When clicking 'Open' a new dialog starts.

-

The macro shows the last saved view to the selected workbook (picture 4) and a dialog for selecting worksheet [where to copy and paste an equation] (picture 5). Here we will select worksheet 'Sheet1', and click 'Proceed' button.

-

In picture 6 cell 'H2' of the table shown in picture 4 will be selected. The cell contains an equation: '= F2 - G2'. We click 'OK'.

-

The macro determines dimensions of the table, and informs user about them as well as about the cell that contains the equation due to be pasted (picture 7).

-

After the run the macro asks the user, whether he/she would like to copy and past more equations (picture 9). Answer 'Y' to continue copying and pasting, and 'N' to stop the run.

Results

-

The results of the run are shown in picture 8. Macro has copied the formula into cells from 'H3' to 'H10991'. Would there have been cells with contents, the macro wouldn't have pasted the equation in them, but passed them.

-

The macro informs about the possibility to cintinue copying and pasting more equations in the selected table.

Download

-

To download a workbook 'ExcEdi1a.xls' containing the macros 'AppTab1', 'RemSpa1' and 'CopEqu1' click here.

-

The demo version of the macro is valid till December 31, 2011. When that date has passed, the macro runs only to give a message: 'Macro not valid anymore. Get new one: http://www.f-fs.net'.