create Invoice with Macro

[delta]

i want to create invoice with macro i want when i click button on Work Sheet "Entry" then the highlighted cell data copy in Worksheet "Data" in their related heading . i attache sample file for more clarification so pls help as soon as possible.

 

[Hui]

Delta

Daily dose of Excel held a competition in 2005 for this same topic

It obtained 9 solutions which are shown:
http://dailydoseofexcel.com/archives/2005/10/27/invoice-app-the-results/

I kept a copy of the submitted solutions which you can download and review from here: https://dl.dropboxusercontent.com/u/65728154/invoices.zip

 

[Nebu]

Hi:

Since I already worked on it. Find the attached.

Thanks

 

[pecoflyer]

And of course there is the very powerful Word Mail Merge

 

[delta]

nice answer but i did not understand some code
like res font which cell indicate pls more deep precribe this code whit line to line

.Range("C11:F" & .Cells(Rows.Count, 3).End(xlUp).Row - 5).Copy
Sheet2.Range("D" & Sheet2.Cells(Rows.Count, 4).End(xlUp).Row + 1).PasteSpecial xlPasteValues
.[C4].Copy
Sheet2.Range("C" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1 & ":C" & Sheet2.Cells(Rows.Count, 4).End(xlUp).Row).PasteSpecial xlPasteValues
.[C7].Copy
Sheet2.Range("A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1 & ":A" & Sheet2.Cells(Rows.Count, 4).End(xlUp).Row).PasteSpecial xlPasteValues
.[F5].Copy
Sheet2.Range("B" & Sheet2.Cells(Rows.Count, 2).End(xlUp).Row + 1 & ":B" & Sheet2.Cells(Rows.Count, 4).End(xlUp).Row).PasteSpecial xlPasteValues
Sheet2.Range("H" & Sheet2.Cells(Rows.Count, 8).End(xlUp).Row + 1 & ":H" & Sheet2.Cells(Rows.Count, 4).End(xlUp).Row) = i


Thanks[/quote]

 

[Nebu]

Hi:

.Range("C11:F" & .Cells(Rows.Count, 3).End(xlUp).Row - 5).Copy

I am copying the range from the data entry tab to your output tab dynamically , since your item name start form C11 and may have more or less items per invoice , the above quoted code will count the row items based on column 3 (hence the 3 you highlighted in red)and copy the range and paset in tour output tab.

Sheet2.Range("D" & Sheet2.Cells(Rows.Count, 4).End(xlUp).Row + 1).PasteSpecial xlPasteValues

This above code will paste the values on the last available empty row of the output tab and will not over write on the existing data.

Sheet2.Range("C" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1 & ":C" & Sheet2.Cells(Rows.Count, 4).End(xlUp).Row).PasteSpecial xlPasteValues

since the party name, bill no etc only entered once on the data entry tab and you will be having multiple items on the invoice , you may need to repeat these values based on the no's of items on the invoice, the above code will fill in the party name based on the no's of items on the invoice on to the output tab.

Thanks