Copying certain cells from each row to another sheet for invoice

    Re: Copying certain cells from each row to another sheet for invoice


    Hey dude!


    I managed to have a play with the workbook this morning and got to a point (with simple formulae changes,no coding!) to something that Will be an awesome tool for this company!!


    The only issue now lies with clearing the invoice tab (of just the materials and their cost from the sheet when needed) It would be awesome to have a button on the tab to clear the contents. (These content are in cell C19:C39 D19:D39 E19:E39 F19:F39 G19:G39 H19:H39 I19:I39 J19:J39 K19:K39 L19:39)


    I have attached the latest file with the changes I have made. I hope it hasn't mucked around too much with your overall model!)


    The way i imagine the workbook to work now is...


    I enter the stock used


    I click "new invoice"


    I enter labour charges in the table at the bottom of data tab.


    I click onto the template tab.


    I print hard copy


    I save file


    I press "clear invoice" button on template tab.


    I re-save


    Close file


    Reopen last saved file to repeat the process.


    hope this makes sense, im sure i can manage for the time being with the model i have, if you are sick of changing this thing for me!


    Thans mate
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Hey man,


    I also get this error Sheet4.Cells(19, arr(i)).PasteSpecial xlPasteValues


    upon clicking the "new invoice" when there is 0 in all QTY cells C6:C300.


    If this error could be eliminated this is how i could reset the template to its original state.


    cheers Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Hi
    Re the second question, why on earth would you go pressing a button when you know full well there has been no change to the quantity. There has to be a level of resonableness and common sense built into every project. Is the following idiot proofing even necessary? :confused: The following makes the assumption that you have a total in D301.



    Take care


    Smallman


    Re: Copying certain cells from each row to another sheet for invoice


    I get exactly what you mean now! sorry mate i just have my head up my arse with all the workload ive got at the moment but it is clear as day now ive had a bit of time to run through it.


    We got their eventually mate! i can not thank you enough for the time effort and patience you have given me over the past weeks or so. You are a credit to this site and if their is anything i can do for now or in the future please ask!!


    regards
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Hi Mike



    The above seems an awfully convoluted process. I think down the track you would run the model in this manner. Enter the labour data. This is your input. Now you are ready to do all of the above. You click a button and everything is done for you. I wouldn't bother doing anything manual other than cell input to populate the model.


    You must be getting a bit tired of the process but the clearing of the invoice sheet already happens in the create Invoice button. Now putting my Rocket scientist hat on I will go bounding into the wilderness and make this very loose connection - As the code already exists in the prior process I will create a new macro on the Invoice tab and use the executable line from the create invoice process.


    So doing this will go a little like this;

    Code
    Sub LeapofFaith()
Sheet4.Range("C19:M40").ClearContents
end sub


    Now I have not tested the above but I will take the Pepsi challenge it does what you want. Cool so just a matter of doing the above list and you should be well on your way.


    Take care


    Smallman

    Re: Copying certain cells from each row to another sheet for invoice


    Hey mate!


    Yeah its been a great start to the week so far knocking out the invoices and changes in stock like there is no tomorrow! thanks again!! Ive copied the work book to run as a quoting tool :)


    All i have done is change the data on the template tab to show "quote" rather than invoice and I can generate a quote with just as little effort as an invoice!!


    However I have a issue with new entries I have added in column A108< (A108:A300) Any item in cells A6:A107 transposes perfectly to the template tab.


    The new items do not transpose onto the template tab when i press the "invoice" button on the data tab. I have tried to change the code but i'm not having much luck. I have attached the file with this message if you have anytime to spare me.


    Thanks Heaps this workbook is awesome!!!
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Hi Mike


    Your problem is with this line.


    Code
    Range(Cells(6, ar(i)), Cells(100, ar(i))).Copy


    Change to the following in the module.


    Code
    Range(Cells(6, ar(i)), Cells(300, ar(i))).Copy


    That should sort you out. Let me know if you need anything further.


    Take care


    Smallman

    Re: Copying certain cells from each row to another sheet for invoice


    Hey mate!


    I was wondering if you was up for giving me a bit more help? The data tab is getting quite muddled up and its not exactly logical to work through at the moment. I am wanting 4 data tabs with up to 300 item entries (A6:A306) each tab would have a different name. Cables,fixings,misc,hardware.


    I have been asked by the director of the whole company to implement these changes so the work can be used by all the installation companies!!!


    I have been enquiring for the last hour or so to try find someone local to help us with this, and no one seems to be able to write code for excel! So..... the company is prepared to pay for the extra work on the workbook!


    Let me know if you can help :)


    Mike

Participate now!

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

Register Yourself Login