Modifiy VBA code to paste to Next available Colunm

  • Re: Modifiy VBA code to paste to Next available Colunm


    Try the attached file. No need to send password, I used a different method.


    There is an extra, hidden, sheet called "Template" that holds two templates for the tables that are used for the forecast summary, each template is a defined named range. These are copied to the Forecast Summary sheet when the code runs which means that the table formats for borders and number formats are set for each table.


    If you want to change the table formats then make the changes to the templates.


    There is a button on the Summary sheet, click it to run the code.


    This is the code in the file

  • Re: Modifiy VBA code to paste to Next available Colunm


    Woooooow, You are just awesome.
    Everything is working great except for two sections.


    When I runt the code on all 17 files, there are two errors that I have highlighted in Yellow. The column A description for "Melbourne" is blank. I tired filing it my self and running the macro but they go blank again.


    Also the "service" section of File, "Townsville", Also highlighted yellow, is not coping the correct data.


    I tired sending all 17 files through here but size is too big. (1.68 mb). If you need these, send me your email so I can send them to you.


    Thanks

  • Re: Modifiy VBA code to paste to Next available Colunm


    Can you attach the files for Melbourne and Townsville.

    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: Modifiy VBA code to paste to Next available Colunm


    Hi KJ
    See attached.
    I also found another issue that is my fault. in the service section, I missed the "Overhead" subgroup. the corresponding cells for this section are yellow as well. My apologies for missing this part.


    I added these lines to the template and ran the macro. however, the values do not get copied.


    I checked all my files and all are the same so unsure what the problem is.



    So the service section should look like this:


    [TABLE="width: 431"]

    [tr]


    [td]

    Service

    [/td]


    [td][/td]


    [td]

    Forecast

    [/td]


    [td]

    Budget

    [/td]


    [/tr]


    [tr]


    [td]

    Breakdowns

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Average Labour $ / Job

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Average Parts Sales $ / Job

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Estimated Number of Jobs per Day

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Average Jobs per Day /Technician

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Breakdown Labour GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Breakdown Parts GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Preventative Maintenance

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Average Labour $ / Job

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Average Parts Sales $ / Job

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Estimated Number of Jobs per Day

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    - Average Jobs per Day /Technician

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    PM Labour GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    PM Parts GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Overhauls

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Overhaul Labour

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Overhaul Labour GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Overhaul Parts

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Overhaul Parts GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Internal Work

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Hours Per Month

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Internal Labour Rate

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Internal Work Labour GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Vans Required

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Average Vans on the Road

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Other Revenue

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    DA Parts Sales

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    DA Part Sales GP %

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Battery Sales

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Battery Sales GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Driver Training Sales

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Driver Training GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Contract Service

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Contract Service GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Fleet Service

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Fleet Service GP%

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Overheads

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Wages & Allowances

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Overtime

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Training

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Non Productive

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Call Centre Recharge

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Rate Variance

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Vehicles

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Net Freight

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Commissions

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Warranty

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Environmental Charge

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Sundries

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Modifiy VBA code to paste to Next available Colunm


    OK got the rest of the files via e-mail.


    Problem with Townsville was that one of the yellow cells on Rentals sheet was bright yellow not pale yellow like all others.
    Problem with incorrect layout was caused by the last row of the table above have no values, this has been corrected by replacing those 2 cells, if empty, with a 0.
    New lines added to the templates and Named Ranges changed to Dynamic Named Ranges.

  • Re: Modifiy VBA code to paste to Next available Colunm


    Hi KJ
    Sorry to bother you again, but when I run the macro, it opens the first workbook then does nothing. I tried running it couple of time on different number of files but same result


    When I first opened the file, it asked me to enable editing which I did but not sure if this effects the macro.


    Is there something I'm doing?


    Thanks

  • Re: Modifiy VBA code to paste to Next available Colunm


    So sorry, my mistake, I was testing it with individual workbooks rather than creating a new folder with all workbooks.


    Remove this line of code


    Code
    Set wbIn = Workbooks("April 2015 Melbourne Forecast")


    Don't worry about the 'Allow Editing' when you save the file the reopen that should go. If it does not the try saving with a new name.

    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: Modifiy VBA code to paste to Next available Colunm


    Hi KJ
    I removed the line but still the same.


    The code after I removed the line you said to remove is below.


  • Re: Modifiy VBA code to paste to Next available Colunm


    Sorry forgot to change code to suit change of named range to dynamic named range so it errored and the error handler exited the code. Fixed.


    Further testing on all Branch files threw up another error by you. Check he file for Brisbane, Fleet sheet the Overhead Forecast values are pink, should be yellow.


    All working OK when I tested with all files. I did get a message about updating links for one file but that should not happen for you because the link shoud be on your system.


    Change the code to this

    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: Modifiy VBA code to paste to Next available Colunm


    Hi KJ
    Code is working perfectly.


    Thank you for taking the time and helping me. You are a legend.

  • Re: Modifiy VBA code to paste to Next available Colunm


    You're welcome

    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: Modifiy VBA code to paste to Next available Colunm


    Hi KJ
    On a separate questions but related to the files we were working on, How can I create a drop down list of the dates that are on top of the master workbook but skip the date that has a B at the end of it.


    thanks

  • Re: Modifiy VBA code to paste to Next available Colunm


    As this is a separate question, even though it concerns the same file it is a different issue all together, so you need to start a new thread for this question.

    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: Modifiy VBA code to paste to Next available Colunm


    Hi KJ
    I have been running the data for some days and thanks to you everything is working well.


    However, I have found that some data that are in white cells are also required.


    if its not too much to ask, could you modify the code so it copies non-blank white cells as well as yellow cells. Some of the cells have formulas in them so its best to copy them as values.


    Let me know if you need the files again and I will email them to you.


    Highly appreciate your help.


    Thanks

Participate now!

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