AUD $500 - Modify existing code to consolidate into and streamline

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    S.O. developed some code for me. I want to take it another step further and, if we can, integrate the existing code to make this as much of a 1 click option as we can.


    It takes me considerable time to complete this work every Monday and I am unable to hand it off at the moment. Hence the decent payment amount. I do not think this is hard to do, just that I really want it done :)


    With the work S.O. has already done I am left with up to 20 spread sheets similar to the 40405-P084 - file attached. Layouts are the same, just the number of week endings in Col F will differ.


    I want to take all those sheets, and consolidate into 1 single workbook - as shown in the attached file Ozgrid Full Statement.


    Background info and the original work can be seen http://www.ozgrid.com/forum/showthread.php?t=194330


    forum.ozgrid.com/index.php?attachment/65788/forum.ozgrid.com/index.php?attachment/65789/

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    I am under no set time limit as such and our geographical separation will add delay anyway - which is not an issue. I would love to have it up and working robustly by 3rd week of July. I am due to go away at the end of July and really need to be able to delegate the process so that I do not have to log in remotely.


    My vision for this is that once I have collated the required files from my database, I am then able to activate a macro / script and the output will be as shown in the full statement above (slight edit to that coming up but that is an easy fix).


    If you can get me there in 3 or 4 weeks the work is yours.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Timeframe is ok with me.


    My current understanding is that:
    1. the macro from SO generates about 20 files similar to " 40405-P084" (number of files can change)
    2. all generated files are in the same folder


    You need a macro :
    1. Regroup all files in only one. (one sheet per file) (Do you need a specific name for the "master" file?)
    2. All files (sheets) should be reformat :
    - Divide data per week (group rows in G)
    - Add header for each group of data (Invoice No, Passenger, etc...)
    - Add total for each group of data (week)


    I'll review our requirements and the code by SO tomorrow and let you know if I have any questions.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Correct. Points to note:


    • SO's code takes sheets that I create and cross-references with a master. It removes any invoices from the individual sheets and the result is the sheet seen in 40405.
    • At this stage the entries in Col G are incorrect. As the SUM was done prior to the invoice removal stage. Col G requires a SUM for each week ending
    • The W/ Ending dates in Col D should reflect the date in Col F for each week ending. Originally there was only one header hence only one date.
    • For some reason - SO's code fails with a runtime if my master is anything other than .XLS - if we can find a way around that it would be great but not a deal breaker.



    That's it I think. :)

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Hello,


    I have a working solution for you but I've only been able to test with the first file you provide in the first thread. Everything seems ok and I've fixed the problem about the XLS file.
    Either you send me more samples files so I can make more validation or I send you the macro as is and you test on your side.


    Almost 1AM here, will check back tomorrow morning...

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    ok GC,


    Send me the macro and I will test my end tomorrow. Thanks

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Hello,
    Here's the "MASTER" file with the macro.


    1. Click on the button you'll see in Sheet1
    2. The code will ask you to browse and select the "Cost code" workbook
    3. Then, the code asks you to browse to the folder containing the "Cost centre reports"
    4. Wait and enjoy...!


    Each cost centre reports will be modified as per SO macro and saved with the new format you want.
    At the same time, each report is copied to the Master file.
    At the end of the macro the Master file is renamed "Master_2015-06-25.xlsm" (with the date) and the first sheet with the button is removed and replaced by an Index (I thought it would be useful be it can be easily removed).
    To start again, reopen the "MASTER" file and click on the button.


    Just let me know if you have any comments or find any bugs...

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Hello,
    Did you received the last 2 Private messages I sent you? If not, can you check if you inbox is full.
    As requrested, I sent you my email address so you can share the dropbox links for the files.


    Thanks.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    yep. was away for the weekend. You should have an email.


    Cheers

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Hi,
    I got your email and send you a new version of the file via the dropbox link you provided.
    Let me know how it goes...
    Thanks.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Looks good on the test files that I sent you. I will do a full statement run hopefully this afternoon, tomorrow morning at the latest and will make payment if all ok. I do not expect any issues.


    Thanks for your effort.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Run time error


    formulaRange.FormulaR1C1 = "=IFERROR(MATCH(""Total for INV ""&RC[1]&""*"",'[" & costCodeWorkbook.Name & "]cost code'!R1C2:R65000C2,0),""DELETE"")"


    I have uploaded the current files into dropbox, including the new Cost code statement. I read your notes in the code as to the reason this error would be thrown up however all files including the cost code file are .xlsx


    Thanks

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    Hello,
    I've uploaded a new version of the Master file on the dropbox folder.
    The problem was that the name of the cost code report was not "cost code". I changed the code so that it will work whatever the name of the sheet is, as long as it is the first sheet in the workbook.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    No joy. Run time error 438...Object doesn't support this property or method.


    costCodeWorksheetName = costCodeWorkbook.Sheets(1)


    I have tried it with files named 'Cost code' and 'Report' and the same error is encountered.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    My apologies, I uploaded the wrong version of the file.
    The line should have been

    Code
    costCodeWorksheetName = costCodeWorkbook.Sheets(1).Name


    I've just uploaded the good version on Dropbox. It should be ok.

    Re: AUD $500 - Modify existing code to consolidate into and streamline


    That ran this weeks statements perfectly. Thanks.


    I will make payment today. I am away for the rest of the week and although I do not expect any issues when I return may I Approach to for any bug fixes if they arise?


    Thanks

Participate now!

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

Register Yourself Login