Auto generate sales ledger from invoices

[Neelie]

Ok So this is the first time I've used a forum so please be patient.

I want to generate an invoice in Excel which will update to the next Invoice number when I save it. Also what I need is each time I use a new Invoice I'd like it to be transferred into a Sales Ledger but only using certain fields. Invoice# Date Customer Net amount Tax & Total. So I have an updated list of Invoices.
I have created worksheets Sales Ledger, Sales Invoice, Customers, Stock Codes. I have used VLOOKUP to link the Invoice with Customers & Stock which is working.
I haven't a clue how to write macros and only have basic knowledge of functions.
Hope someone can help. Thank you

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Neelie]

I don't have any macros. As I said I don't have a clue how to do one. I will do as you suggest. Thank you

 

[alansidman]

I believe that Mumps was making a comment that if you happened to have any macros, to be sure to include them. Since you don't, and since we cannot see inside your head, it will be advantageous to follow his instructions and load a copy of a sanitized version of what you have and a mocked up version of what you want to see as a solution. Then we can tailor a workable solution. Without that information, any solution offered will be like throwing darts while blindfolded and being spun in circles a couple of time.

 

[Neelie]

https://www.dropbox.com/s/i2r8d99bcuae9gv/Accounting.xlsx?dl=0

Sales Invoice Sheet B1,F1,C3,C4,E18,E19,E21 transferred to Sales Invoice Journal sheet A2 :G2 and so on...Delivery column has been deleted since I uploaded.
Presumably I would need to 'Save' each individual invoice before this would work. How can I do this?
After doing this I need the next Invoice# to generate automatically.
Hope the link works. I found Dropbox a bit confusing
Thanks

 

[mumps]

Currently in your invoice you have 2 products listed (103 and 109). Can I assume that the numbers of products will vary? What will you do with the Invoice after it has been completed and the data copied to the Sales Invoice Journal? Will you print it or do you want to save it?

 

[Neelie]

Oh! So pleased that worked.
Yes More products & customers will be added.
I would like to save it if possible.
I am trying to learn and spent weeks trying to do this.
Thank you

 

[Neelie]

Thank you for the advice - much appreciated

 

[mumps]

OK. Do you want to save only the "Sales Invoice" sheet as a separate file or do you want to save the entire workbook? What name would you like to give to the new saved file? You could use the customer name and/or the Invoice #. What is the full path to the folder where you want to save each invoice?

 

[Neelie]

Just need to save the invoices as a separate file. Customer name

C:\Users\Eileen\Desktop\Sales Invoices