Cost Centre Project, Add Loop Funcionality, £60 GBP

  • Hi



    I have a excel VBA project with limited annonmised data for working draft purposes, please see attached.



    What I have


    My Project currently does the following:


    • Raw data is download from the Accounting ledger and added to the blue tabs
    • On the purple tabs a report is collated based on sumif's from the raw data. The criteria are in cell A6 "Cost Centre & CellA10 "Period"._ Sheet"CC Rep".
    • There is a pivottable on sheet"Line Item". This has selction change code on the sheet so that the pivot table updates from cell C9 "period" & Cell C10 Cost Centre.
    • There are two macro buttons on sheet"CC REP", the first copies the report on this tab to a new worksheet and renames it, the second takes the pivot table in sheet"Detail" and creates to new sheets. One a values only copy of the pivottable and the other the drilldown detail of that pivot table.


    What I need


    This works just fine, what i need help in doing is the following:


    There are about 56 Cost Centres that this needs repeating for. It is time consuming and labourious. I tried to automate with a loop, but he output was 56 reports all correctly named showing the same data.
    Also I could not get teach cost centre to produce the three reports from one macro?


    What i would like is as follows:


    • My VBA adapting with explanations (I need to understnad this and takeownership)
    • All three reports can run off one button
    • There is the added function to produce all 56 Cost centre reports from one loop function, showing the correct data for each report.
    • The splitwork book macro to pick up only the report sheets
    • The pivottable drill down detail sheet to be formated as £100.00




    Code/ macros used

    • Copy Report
    • Copy Detail
    • Selection change code to update pivot
    • Delete Reports
    • Split workbook



    UpdatePivot




    Delete Sheets Code





    Copy sheets code





    Pivotdrill Code





    Split work book Code





    To clarify


    • I must be able to follow, so will need explanation of code
    • Would prefer my code adapting to work in a loop
    • Must give corect data for each report.


    Let me know your thoughts before jumping in an doing any work as I want to be fair.



    Matt



    forum.ozgrid.com/index.php?attachment/71253/

  • Re: Cost Centre Project, Add Loop Funcionality, £60 GBP


    I can look at this for you.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Cost Centre Project, Add Loop Funcionality, £60 GBP


    OK that will be fine.


    Though review for feasibility before you do any work on the project. I wan to give you the opportunity to feedback before you start working.


    Cheers


    Matt

  • Re: Cost Centre Project, Add Loop Funcionality, £60 GBP


    Your file is ready, you should already have my PayPal details but I will send it to you via a PM anyway.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!