Copying certain cells from each row to another sheet for invoice

Hi guys,

Had some great help on here from certain individuals, just after a little bit more as im stumped!

My spreadsheet which tracks my stock is attached.

What i need to happen is that if any one item is selected in column B6:B100 from a value of 1< i need information out of cell A6 B6 I6 and J6 Copying in to a table format on Sheet 2. This is so i can print the invoice off sheet 2 without the customer obviously seeing my materials costs and stock etc.

It would also be great if i could get the labour costs (net,gst and Gross) in cells F106:F108 into the the new table on sheet 2.

Any help would be greatly appreciated!!

Thanks Mike

Re: code wanted for copying certain cells from each row to another sheet for invoice

hey,

Sorry for my confusing post it sounded clear in my head but re-reading it I see it isn't!

The way i would like it to operate is..

Say i put a value from 1< in column B6:B100 this is in the QTY column.

on a typical job i may fill out 10< cells in column B depending on what materials i use for that particular job.

For arguments sake lets say a value of 5 in B6 (VGA 1m)
4 in B7 (VGA 2m)
2 in B10 (VGA 10m)

What i want to do is transfer the data i need ((eg ITEM, QTY used, NET CHARGE OUT, GROSS CHARGE OUT) only of the materials i have used) not all the other information in that row eg NET PRICE IN

I want this info to copy itself into a invoice template so it saves me the hassle of invoicing the job manually.

The sheet number 1 has code which you generously provided yesterday, so that upon saving the document the QTY column reverts to 0 and the stock is automatically adjusted and saved. (thanks again for this!!!!)

So when the document is saved i need the invoice sheet to clear itself so i have a blank invoice template for my next job that i will invoice.

I hope this is a little bit more clear in my requirements, probably not!

Thanks
Mike

Re: code wanted for copying certain cells from each row to another sheet for invoice

Quote from Smallman;601417

Hi

Try this for updating your Invoice sheet. I have attached a file with the changes to the Save procedure.

Take care

Smallman

Code

Option Explicit
Sub test()
    Dim i As Integer
    Dim urng As Range
    
    Range("B5:B100").AutoFilter 1, ">0"
    Set urng = Application.Union(Range("A5:B100"), Range("I5:J100"))
    urng.Copy Sheet2.Range("A4")
    Sheet1.AutoFilterMode = False
End Sub

Display More

Mate! you are a diamond!! that works!! However if i could just trouble you for one more adjustment? you can tell me to go f-off as i've probably been a pain in your backside today! but i'll keep my fingers crossed.

I have attached the invoice template i use at the moment. Could the invoice data be put on to this new template instead of the sheet 3?? also can the info from sheet 2 (calcs) be entered on the same new invoice template (so basically all materials plus labour is on the invoice?

Thanks for your help so far!!

Re: code wanted for copying certain cells from each row to another sheet for invoice

sorry i dont understand what you mean by "However where is the data tab. Which template? They all look the same to me?
"

Thank Mike

Re: code wanted for copying certain cells from each row to another sheet for invoice

ps you can make any changes you want!!

Re: code wanted for copying certain cells from each row to another sheet for invoice

Quote from Smallman;601436

Hi Mike

There was a sheet in your previous model called "DATA" which I made the bold assumption would be feeding these templates? In the file you posted there is no sign of this DATA tab. Without the source data what exactly do you want to do?

Take care

Smallman

Display More

I apologise for my ignorance in advance! but i'm still confused. The latest excel sheet you attached for me, in which you created the invoice sheet to update on sheet 3 when you hit the "create invoice". (awesome by the way!!!)

I would like the Invoice to update on the New invoice template i sent you. (the newer sharp one!). so my template replaces the small table on sheet 3.

Today i will by updating the items in column B6:B100 as i have a full pricing list from our suppliers.

So on the new invoice template (sheet 3), i would like the materials (sheet 1) to update as well as the labour costs on (sheet 2)

So it haves me time at work!!

If you have to change the invoice template i sent you, by all means do!

I hope this makes sense, fingers crossed!

Many Thanks
Mike

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

Quote from Smallman;601498

Hi Mike

Changes I would recommend are not merging of cells particularly where data is to be imported. Centre Across selection has the same look and feel without the loss of fidelity. It simply defies belief the microsoft continues to create products with Merging cells as an option.

Your references to SHeets 1, 2 and 3 are confusing as your original file did not have any data on these sheets. Attached is a book with 4 sheets. Data, Calcs, Invoice(Redundant) and Template. If I understand you correclty. You want the information in the data and calcs sheet to populate the Template sheet?

If I was running this project I would have one template sheet and update all the sales data. Then I would run a process which spat out 30-100 whatever unique templates and have them printing while I had my feet up sipping a coffee.

Take care

Smallman

Display More

Hey Smallman,

Thanks for your help again you are a godsend!

I think that i'm being useless at describing the processes i want this workbook to carry out. I see you are in brizzy, would it be possible for me to call so we could have a quick chat? I understand you are taking the time out of your day to pacify an annoyance like me!, but I would be truly greatful for anytime you can spare. If you want to keep a nut case like me at arms length I totally understand!

I really need this workbook to be up and running by This Fri, as the newly formed company starts trading on the following Monday. I've recently been given a promotion from the tools to Management, and being only 26 i am wanting to blow their socks off and show them what i'm made of ( minus the actual excel skills!!) Once this workbook is completed, I have a track on stock and labour. A process that was not monitored in the current company. It will be of massive benefit to me as a tool to run my installation guys, keep track on stock and invoice.

I have a larger vision, somewhere hopefully not too far in the future for a complete automation program (customer database,stock inventory,invoice, this will be online where the guys in the field can use ipads to invoice a job as soon as it is finished.Entering various bits of information for customer details which are then saved to a database for future jobs, materials are updated in realtime time when they are used and then an invoice can be emailed to me upon completion of the job! when stock reaches the trigger level an email will be sent to our suppliers requesting the items needed) long winded but i got there, phew.

But for now this work book that we have been working on, will be priceless to me for the next few months. Learn to walk before I can run yadda yadda...
Sorry about rambling but I just wanted you to know that so far you have helped me so much, and i appreciate it.

Huge Thanks
Michael

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

"If I understand you correclty. You want the information in the data and calcs sheet to populate the Template sheet?"

Yes this is what i wanted!!

Thanks
Michael

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

Quote from Smallman;601655

Hi Mike

I was out and about last night so could not be online. I understand your dilema. Happy to make you look like a Rock Star. I really hope you do well in your new role. I will have a look at your file with this new information.

Take care

Smallman

Display More

Hey Smallman,

Thanks for your time again! i tried to be a little proactive and change the code to allow more than 100 items in the stock list. I went into the code and anywhere there was a the number 100 i replaced to 200 (to try and increase the item list to 200!) but to no avail it would be cool if the sheet could handle upto say 300 items. Ive pretty much used 100 already but we need more for future updating.

Cheers mate
Michael

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

Quote from Smallman;601704

Hi Mike

Have a look at the file and the changes I have made. The Description on the Template sheet, is this the Item No? I have changed your save procedure. Taken it out and replaced it with a fresh process which allows some error trapping for inadvertantly hitting save.

The Template sheet is a bit messy and this has an impact on the code required. Having said that I understand why it is messy. I got round this by adding a couple of variants with the columns I assume are going into your Template. Don't worry if I chose the wrong ones this is just a proof of concept and it is starting to take shape.

Have a look and get back to the forum. The new code is below.

Take care

Smallman

Code

Option Explicit
Option Base 1
Sub PopTemp()
    Dim i As Integer
    Dim ar As Variant
    Dim arr As Variant
    
    Application.ScreenUpdating = False
    ar = Array(2, 1, 9, 10) 'Cols to transpose
    arr = Array(3, 4, 12, 13)
    
    Range("B5:B100").AutoFilter 1, ">0"


        For i = LBound(ar) To UBound(ar)
            Range(Cells(6, ar(i)), Cells(100, ar(i))).Copy
            Sheet4.Cells(19, arr(i)).PasteSpecial xlPasteValues
        Next i
    
    Sheet1.AutoFilterMode = False
End Sub

Display More

Display More

Hey Smallman,

The new concept looks and functions heaps better! in sheet 1 there are 23 items in A6 to A29. I have a current list of 70+ items in one of the older models we have been working on. This list i expect to grow to over time so i was wondering if we could apply the code/formulae for say 200 items?

Thanks
Mike

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

Oh sorry about that, It makes sense a lot of sense to do that! So is the current model ready for me to input my data? and use as a finished product? I was thinking of inserting a 2/3 new columns. Maybe for the name of the supplier, and product codes. If I do this will it effect the code/function of the work book?

Thanks
Mike