Solved: Excel accounting spreadsheet macro

[anovice]

Hi everyone ! Been reading this forum for ages and gleaned loads of information but can't find a solution to my problem.

I run a small business and have set up the accounts on a spreadsheet. Managed to work out functions etc to transfer info from one worksheet to another but I need a macro (Ithink) to automatically 'log' info from an invoice. I would like ideally to auto list date, inv no, net, tax, total from the invoice onto another worksheet row by row when the Invoice is saved.

I have managed with great difficulty to write a macro to 'Save' from a button. I have shown this below.

[VBA]Sub Rectangle17_Click()
'
' Rectangle17_Click Macro
' Macro recorded 21/09/2008 by Eileen
'Dim Lst As String
Lst = Range("E6").Value & "_SalesInvoice.xls"
ActiveWorkbook.SaveAs Filename:= _
Lst _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub[/VBA]

Is it possible to add to this and log the invoice also?

Have tried SUMIF "E6" = a certain inv no but the function isn't recognised until the inv is created so can't copy function down the rows !

Sorry if this is a long drawn out post.

Thanks for reading this.

[RonMcK]

Eileen,

Would it be possible for you to upload an Excel file? Include your code in a module and a sample of the format you wnat to capture the invoice data into with a line showing sample data. Include a second sheet with the invoice for that sample data so we can see what goes where.

[uvba]code[/uvba]

Thanks,

[anovice]

Thank you have attached Sample (I hope)

[Bob Phillips]

Eileen,

ARe you saying that you will create an invoice, on the Sales Invoice Template (which I then presume that you print and send to your customer, and then you want to transfer the data from there to the summary?

If so, I would do it the other way around, have all the data in the summary, and a simple entry of the invoice number on the invoice would pick up the other data. Table driven.

[RonMcK]

Eileen,

If you have 10 lines of detail on your invoice, do you want those 10 lines to appear in your invoice register, as well?

Just wondering,

[Bob Phillips]

Ron,

My thinking that it is far simpler to capture all of your sales information in one go in a table, i.e. a spreadsheet, and then automate the invoice production. It just seems simpler and more logical to me.

My application model always go, inputs -> data store -> outputs, and the invoice is the output here.

I certainly wouldn't argue about having some VBA that opened a template and transferred the invoice data to it and printed it, but that is probably unnecessary for Eileen's needs here, and it can be doen with worksheet formulae.

[RonMcK]

Bob,

If I'm following you, the invoice template exists as a worksheet; this worksheet is linked to the invoice register worksheet and is populated with data from the invoice register based on the invoice number Eileen will enter on the invoice. The invoice template worksheet is printed with the copies being distributed to the customer and Eileen's files.

Am I correct in assuming that no electronic copies of the individual invoices is retained since she can re-create it by re-entering the invoice number on the invoice template?

Thanks,

[Bob Phillips]

Exactly, on all counts. I would never save the invoices as data, they are not data, just a view of the data, and are re-creatable.

To be honest, I wouldn't save the invoice price or total either, just the VAT rate, the number and the unit price, don't save anything that can be calculated/derived.

[RonMcK]

Bob,

Thanks. Good points.

Cheers!

[anovice]

Hi Guys

If you read my SUMIF function then the first row will disappear when I change the Inv number. This happens because I'm overwriting the invoice each time and saving it.

I like the idea of doing the table first and linking it to the invoice but then my VLOOKUP customer contact link would be a bit finnicky.

I just need the first item 'sold' on the invoice - it very rarely has more than that.

Thanks for your help (up to now) will fiddle around auto entry for invoice from table.

[Bob Phillips]

No, it is very simple.

[anovice]

Thank you so much XLD !

Not so simple for me

[anovice]

Hi ! It all was working great but now I find I need more items on Sales Invoice. Any ideas ?

Thanks

[RonMcK]

Hi ! It all was working great but now I find I need more items on Sales Invoice. Any ideas ?

Eileen,

How many lines do you want to add to your invoices?

Thanks,

[Bob Phillips]

Like this?

[anovice]

Exactly like that !

Molto Buono !

[anovice]

Hi

Thanks for your help so far but I need more rows on the Sales Invoice Summary. As far as I can work out the function takes it to row 20. Have tried to alter the function but it doesn't work.

If you could explain how to increase rows I can adjust when required.

Thanks

[Bob Phillips]

Wherever the formula refers to $A$1:$A$20, change that toe $A$1:$A$n catering for all you will ever need.

[anovice]

Tried that XLD but still won't work. Sorry to be a pain - it's prob something obvious.

[anovice]

XLD are you there ?