Posts by Mattador


    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


    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.



    I have some code that will automatically create the drill down data on multiple pivot tables and extract the data to separate sheets.

    This is gonig to be put in a loop and done for multiple reports, hence not manually clicking into the drill down field.

    My question is as follows:

    I want to auto format each sheet as it is created so that the currency values in a specific column are formated to 2dp .i.e. '199,88.22'.

    I have tried the following code, but ih no effect

    .Columns("E:E").NumberFormat = "#,##0"


    Can anyone correct this code?

    Full code below:

    Thanks in advance

    Re: Automatically pick up showdetail field of pivot table, £5


    I think we are missing each others point.

    To help find common understanding I will change how I explain my position.

    To help I have attached a fresh work book with illustrated notes and a screen shot for visual reference.

    Screen Shot


    As the cell highlighted red will move up and down depending on the number of Gl codes in a cost centre I need code that automatically selects the grandtotal drilldown field.

    I thought I had communicated this clearly, obviuosly not. If you feel this is moving the goal posts then let me know and we can come to some amicable arrangement.



    Re: Automatically pick up showdetail field of pivot table, £5

    It's not in the mock up i sent you.
    If you want me to send a live file I will have to get some data from work and clense it/ tyle over, so that it has nothing confidential in.

    If you need this it i can't access the data until tomorrow night.
    That said it could be any pivot table where the row changes and the column is fixed.

    Re: Automatically pick up showdetail field of pivot table, £5

    My pivot will be as follows:

    Filtered by cost centre and month
    Columns = Total
    Rows = Account codes

    As different cost centres have different activity the number of rows in each cost centre and hence length of pivot table will constantly change.

    That will be the only dynamic variable.

    Hope this clarifies.

    Re: Automatically pick up showdetail field of pivot table, £5


    This does not really do what I am asking, because I will not be able to automate the selection.

    In this model column E will always have the pivot table total that I wish to drill into, the row however will change.
    I tried to use the offset function to pick up the grand total in the pivot table and return the correct cell, but could not get it to work.

    Does this make sense and is what I am asking feasible?

    Re: Automatically pick up showdetail field of pivot table, £5


    Not sure I follows that plus it crashes when I run it.

    I have attahced a owrking file that does what I want, except for picking up the end of a pivot table.

    The relevant tab is report detail and the vba is in module 2.

    The other stuff you can igonre.



    I have the below VBA that works with a pivottable of a fixed number of rows.

    What i need is code that will allow drilldown, show detail cell to be selected on a pivot table of any length.
    Currently the VBA is set to cell "I33".

    I am assuming that this is a relatively common vba request




    I have some VBA to automate the pivot drill down as part of a larger VBA project and I was hoping for some guidance.

    Essnetially the code works, but it creates sheets to the left of the original pivottable and I want to create sheets gonig to the right.

    The code, and it does work is as follows:

    I think I just need to add something to get it to output to the right.

    Hopefully this is very simple and I appreciate your help.