PDA

View Full Version : Solved: Excel accounting spreadsheet macro



anovice
09-26-2008, 05:57 AM
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.

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

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
09-26-2008, 07:09 AM
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.

code

Thanks,

anovice
09-26-2008, 07:37 AM
Thank you have attached Sample (I hope)

Bob Phillips
09-26-2008, 07:40 AM
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
09-26-2008, 07:49 AM
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
09-26-2008, 07:57 AM
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
09-26-2008, 08:14 AM
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
09-26-2008, 08:20 AM
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
09-26-2008, 08:23 AM
Bob,

Thanks. Good points.

Cheers!

anovice
09-26-2008, 08:49 AM
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
09-26-2008, 09:29 AM
No, it is very simple.

anovice
09-26-2008, 10:10 AM
Thank you so much XLD !

Not so simple for me :doh:

anovice
10-14-2008, 06:45 AM
Hi ! It all was working great but now I find I need more items on Sales Invoice. Any ideas ?

Thanks

RonMcK
10-14-2008, 07:07 AM
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
10-14-2008, 07:10 AM
Like this?

anovice
10-14-2008, 07:43 AM
Exactly like that !

Molto Buono !

anovice
10-24-2008, 05:48 AM
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
10-24-2008, 06:02 AM
Wherever the formula refers to $A$1:$A$20, change that toe $A$1:$A$n catering for all you will ever need.

anovice
10-24-2008, 07:33 AM
Tried that XLD but still won't work. Sorry to be a pain - it's prob something obvious.

anovice
10-24-2008, 10:26 AM
:hi:

XLD are you there ?

RonMcK
10-24-2008, 10:50 AM
anovice,

You also need to change $A1:$I$20 to $A1:$I$n, where n is the same upper limit to your range that you changed $20 in $A$1:$A$20 to.

Try that and see if you still have problems.

Attached is a copy of the file I modified to all up to 256 lines in the invoice summary sheet

Cheers,

anovice
10-25-2008, 04:26 AM
Thanks for responding Ron.

I've attached your sample 7 that I attempted to alter. As you can see I changed the $256 to $900 and the cell is empty. The same happened when I changed it to n.

What on earth am I doing wrong !

I really only need the row value to reach 1290 but the option to take it higher might be useful in the future.

david000
10-25-2008, 11:56 AM
After you change the number from 256 to 900 press Ctrl+Shift+Enter (Array enter), then turn on your gridlines because you can't see what you need to do next without them.

Select the formula in the top row, and then select the cells below and press Ctrl+"D" (copy down), do that with your gridlines turned on for each column of data.

RonMcK
10-27-2008, 07:41 AM
anovice,

When you change 256 to 900 (or whatever other value you want to use), first, highlight the entire 'line' section of your invoice, then do edit-replace all so you change all instances of 256 to 900. Then, as David000 pointed out make sure that all of your formulas are still 'array' formulas (enclosed in braces {}); if any are not, follow his instructions and fix them.

Attached is your worksheet, fixed.

Cheers,

anovice
10-27-2008, 11:14 AM
Thank you so much Ron, David & XLD !

All working great now.

Much appreciated

Eileen