Saving Invoice for each items separately

[ImranBhatti]

Hello

In the attached workbook in sheet("Invoice") I would like
1)the whole invoice to be saved
on the sheet("InvoiceDatabase").in a way that headings (Invoice date,Invoice#,Customer,
Customer ID,should be repeated for as many times as the number of items
in the invoice similarly the headings bellow the invoice
(Total,Tax Rate,Net,Disc.,Payable,Received,Balance should be repeated for as many times as
the number of items in invoice)
2) I want to insert more rows if items are more than 5
The problem is if I insert rows then formulas will elap the ranges used therein.
3) Serial numbers would be generated as the rownumber minus the number of rows
used in the above headings.
4)The inserted rows should adopt the formating of the current rows.
Since this is a sample the abobe headings would be more than present.

If you have question.Please do ask.

Best Regards
Imran Bhatti

[kasan]

Hi,
Not sure I understand what result do you expect? Could you make some example: filled some values in "InvoiceDatabase" and "Invoice" sheets?

[Arkadi]

This code will insert a row the way you want it to work I think:

Please Login or Register  to view this content.

[Arkadi]

It seems to me all the items in the invoicedatabase are items that reflect totals, there is nothing specific to the individual items... so why do you need to enter the invoice in the database as many times as there are items? It can be done of course, just trying to understand.

[ImranBhatti]

Thanks kasan for your reply.First of all I beg pardon for not putting all the fileds headers in the InvoiceDatabase sheet. I have attached the sample with correction and put the current invoice in the database.I would like the next invoices to be saved there in the similar fashion. Hope now the need is clear.still if my poor English becomes hurdle, I will further explain.

Best regards
Imran Bhatti

[ImranBhatti]

It seems to me all the items in the invoicedatabase are items that reflect totals, there is nothing specific to the individual items... so why do you need to enter the invoice in the database as many times as there are items? It can be done of course, just trying to understand.

Because I would like to generate report(s) for each item .for example an item was sold for how in what quantity.which customer purchased it. on what date etc.

[Arkadi]

Yes, with the new attachment with extra columns it makes more sense.
In Database sheet you have "Total" column with formula sum(D2:H2) D is cust. ID... the formula makes no sense that way?

[ImranBhatti]

Ooops! That was due to copy/paste just to quickly create a sample workbook. All the columns of Database sheet should be filled with a macro.If there were just items,rate qty and amount I would have better copy and paste to the next available row.but I want to other info like Date and cust ID etc.should be repeating themselves against each item.

[Arkadi]

So Tax Amt, Net, etc should also be from Invoice and not calculated just for that item?

[ImranBhatti]

So Tax Amt, Net, etc should also be from Invoice and not calculated just for that item?

Yes.This data will represent the whole invoice .It is just like copying first of all B4:E8 to database then see how many items are there row wise the copy date of invoice and paste against each row in database.then copying cust ID ,total ,disc,etc and paste in the relevent column in database agains each item.

[Arkadi]

Try this:

Please Login or Register  to view this content.

[ImranBhatti]

Try this:

Please Login or Register  to view this content.

It overwrites the headers in Database while saving the first invoice then overwrites the last record of the first invoice if we save the next invoice and so on.not offsetting one row.

[Arkadi]

Oops! My bad...
Replace

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

[ImranBhatti]

Oops! My bad...
Replace

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

Nice .Working now.

I noticed you have put some checks for blanks invoice in the bellow line of code

Please Login or Register  to view this content.

BUT it is saving a blank invoice as well
and For Total the word total is a fixed string why are we refferring to Total in this line

Please Login or Register  to view this content.

[Arkadi]

For the first question, if there are no item numbers in column A, then it should not save anything, if the invoice is blank but Item 1 has a 1 in Column A, then it will still save. If you want to check other areas for blanks too then give me details and we can upgrade the code.

For the second question, because we don't know how many rows there will be, we need to find the word TOTAL to know where total (and the other details below) will be. Then we check to make sure the word TOTAL was found, because if it isn't then the program won't work. The message just explains why the code is stopping ("TOTAL" was not found).

[ImranBhatti]

For the first question, if there are no item numbers in column A, then it should not save anything, if the invoice is blank but Item 1 has a 1 in Column A, then it will still save. If you want to check other areas for blanks too then give me details and we can upgrade the code.

For the second question, because we don't know how many rows there will be, we need to find the word TOTAL to know where total (and the other details below) will be. Then we check to make sure the word TOTAL was found, because if it isn't then the program won't work. The message just explains why the code is stopping ("TOTAL" was not found).

Let me start new thread for that.I have marked this thread solved.Don't want to mark it unsolved again.
Thanks for the offer.

Best Regards
Imran Bhatti

[Arkadi]

Ok, I will keep my eyes open for a new thread if you start one.
Thanks for marking the thread as solved, and I appreciate the rep