Macro – Copy “B” KF to “A” KF by Selecting Columns

Often users want to consolidate (so called composite forecast) their forecast from different sources example marketing, sales, actual billings & statistical forecast. Either they could copy from one key figure or combination of two or more key figures. To do this, they need to copy data from different key figures to one key figure w.r.t. buckets. Example;

In below example I would like to copy first 3 weeks from “Last Year Actual Billing Qty” to Composite Forecast, then for next 4 weeks from “Sales Forecast”, then for next 2 weeks from “Statistical Forecast” and finally from “Last Year Actual Billing Qty”. This means we need to select first 3 columns and last column to copy Last Year Actual Billing Qty into Composite forecast. Same way different columns to copy their respective numbers.

Now we need to define a macro with function COLUMN_MARKED. In below example LYABQ stands for = Last Year Actual Billing Qty. This is just an example macro for this funciton.

Make sure we assign a push button to the macro. This will make sure the macro is visible in planning book when user is processing the data.

Let’s check the effect of macro. Before executing the macro the data in selected KF’s is different for 5 different buckets. We will select only three buckets.

After executing the macro the data in three buckets is same while in rest of the two it’s as before (without any change).

This way we need to define 3 different macro’s with 3 different icon’s to copy the data from different key figure’s to one key figure w.r.t. different buckets.