Excel ‘Conditional Paste’ Add-in

Description
This utility copies a Source range and conditionally pastes it to a Target range with the same dimensions (which can be on the same sheet, another worksheet or even another workbook), subject to user-selected options, as follows:-
• optionally pastes formulae beginning “=SUM(” or “=SUBTOTAL(”;
• optionally pastes cell-formats;
• optionally pastes row-heights (why hasn’t Microsoft included this in their Paste Special?!)
• provides options for pasting of non-SUM/SUBTOTAL cells (e.g. copy any formulae, but ignore other cells).
Other combinations are possible by using this macro more than once (specifying different options) or using Excel's pre-defined Paste/Paste Special functions on the same range.

I wrote this utility back in 2002, to make my life much easier as a FINANCIAL MODELLER. It’s so useful that I felt I should share it with others, so I’ve now polished it up: amended the options it provides, tidied it up and re-written the ‘engine room’ code, to make it more user-friendly, more robust and more efficient.

COPYING COLUMNS COMPRISING A MIXTURE OF VALUES AND FORMULAE
––––––––––––––––––––––––––––––––––––––––––––––––––––
This Excel VBA macro utility was designed primarily to assist FINANCIAL MODELLERS who deal with many extensive columns of figures (e.g. one column for each period), each having a plethora of (the same) subtotals. For instance, this might apply where you have all the detailed ‘line items’ in a Profit & Loss Account, Balance Sheet, Cashflow statement, Tax calculations and Supplementary workings, where various sections will need subtotalling (e.g. Total income, Total expenditure, Total Assets etc.).

Of course, it’s good modelling practice to ensure that formulae are the same right across each row so that, most of the time, you’ll create additional columns simply by copying the first column and pasting across all the columns to the right. But there’s often a need for something different, if only the Total column that sums up all the previous columns. If you plan ahead, you might set up a ‘template’ column, with all the subtotal formulae in (but with all other rows empty), that you can copy and paste to create a new column when you need one. However, almost inevitably, you will add extra rows as you develop your model and, particularly, once you start inserting some real figures into each column. You might then need to change the subtotal formulae in the ‘live’ (populated) column you’re working on, but forget to update the ‘template’ column. So, when you come to create a new column, you face the laborious task of copying across all the subtotals individually, or copying an entire column then laboriously deleting the entries in the ‘detail’ rows, so as to leave just the subtotals. One way or another, even if you don’t need this utility very often, when you do want it, it’s very handy to have at your disposal!

Under good modelling practice, any subtotals should be preceded by a formula in the form “=SUM(…” or “=SUBTOTAL(…”. And, if more than one subtotal is being summed, although one could simply enter a formula “=Cell1 + Cell2” (where Cell1 and Cell2 are subtotals), this would not be good modelling practice: it is better practice to write “=SUM(Cell1, Cell2). So, this utility assumes that all your subtotals follow good practice, and allows you to use a populated column as a template, but retain any SUM/SUBTOTAL formulae. For the non-SUM/SUBTOTAL columns, it provides the following options:-
• do nothing;
• clear them;
• paste only ‘values’, ignoring other cells; or
• paste only formulae, ignoring other cells.

The ability to paste just formulae is particularly useful for such column-oriented models, because you are likely to have many formulae in the ‘detail’ rows as well (e.g. where certain calculated results are re-used lower down, or you are sourcing input data on another Sheet), but you are less likely to want to duplicate any constants. It is safer to have source cells that had constants left blank in the pasted range, so as to draw your attention to the need to fill them with another value.

The utility also comes in handy if you have finished setting up one Sheet (say for Budget figures), then want an exact copy of the layout in which you will substitute Actual or Forecast figures, typically on a separate Sheet. While you could simply duplicate the entire Sheet with a couple of clicks on the Sheet Tab context menu, Microsoft provides no easy way to avoid copying all or many of the cells in the ‘detail’ rows. For instance, if you use PasteSpecial Formulas, it still pastes ‘values’ that aren’t formulas.

DASHBOARDS AND OTHER ‘PRESENTATIONS’
–––––––––––––––––––––––––––––––
When presentation matters, it can take a fair while formatting a table or grid, with different cell formats, coloured text, coloured fills, underlines, outlines etc.. Typically, some row heights and column widths will be adjusted as well (e.g. when creating borders or emphasising headline figures that have a larger font). It’s not uncommon to want more than one instance of exactly the same table or grid, but perhaps with different figures (e.g. when preparing dashboards for executive summaries, you might have tables for: current month, current quarter and year-to-date; or budget, actual and variance). Having spent a long time formatting one, you want to simply copy and paste the formats to the next, to ensure they’re identical. Most of that can be done in Excel with a couple of clicks, but Microsoft still hasn’t provided the facility to copy row heights or column widths. And, as for the columns example above, you might prefer ‘values’ not to be pasted in, so that you don’t accidentally leave in values that don’t apply. So, this utility provides that facility to swiftly duplicate all your intricate formatting, without populating the target range with misleading data.