Planning Template

[exce101]

Hi guys,

I am in the process of running a planning cycle and have currently I have managed to design what the template will look like for one area i.e. EMEA.

We have numerous areas which have multiple cost centres which are further broken down into accounts at hierarchy levels 1 and 2.

I am unable to attach my file as my work has blocked the upload of files but have tried to describe the contents of the tabs below:

MASTER:

• This is where all the cost centres will be listed by region/area (I have only put a small sample)
• Column A has the list of cost centres
• Column B specifies the region / area
• Columns F and G have the list of accounts which will be mapped to each cost centre – this list will not change - there is a set of 21 items in Column F and G

EMEA:

• This is the template I have managed to create based on the EMEA cost centres as per the MASTER tab
• Column A shows the region
• Column B lists the Cost Centres
• Column C shows the allocation rule (please ignore this as I will just apply a vlookup to a mapping table)
• Columns D and E show the set list of accounts to be included with each cost centre as per the MASTER tab columns F and G
• Column F and G will be pulling in the amounts from a SAP query applying a sumifs formula by picking up the amount from column BA based on criteria such as the Profit Centre (column AM), Hier 1 (Column BJ) and Hier 2 (Column BK). If this could be included in the macro, that would be fantastic.
• The layout from columns H to EI will be the same for all regions.

Raw Data:

• This tab will contain the SAP data in the set out format

What I am trying to achieve is a macro automation where the macro will crease a tab for each region in the set format and include the number of cost centres for the respective regions in the template as per the layout in the EMEA temple. So effectively rathat that having to create 21 rows for each cost centre (and there could be over a 100 cost centres for each area), i would like the macro to do this for me.

So for example currently the EMEA region has 7 cost centres, I would like the macro to create a similar template for the Americas region in a separate tab for the 21 cost centres in this region.



Appreciate any help!

Thanks in advance.

 

[Peter_SSs]

Thread closed at OP's request.