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]

Click here to download your file. When you click the "Save Invoice" button, the invoice will be saved, the data in the "Sales Invoice" sheet will be copied to the "Sales Invoice Journal" sheet, the invoice number will be increased by 1 and the data cleared for a new entry.

 

[Fluff]

@mumps
Can you please post the code the site, as per forum rules
Cheers

 

[mumps]

@Fluff
My apologies.

Sub SaveInvoice()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, SubTot As Long
    Set srcWS = Sheets("Sales Invoice")
    Set desWS = Sheets("Sales Invoice Journal")
    SubTot = srcWS.Range("D:D").Find("Subtotal", LookIn:=xlValues, lookat:=xlWhole).Row
    With desWS
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 7) = Array(Range("F1").Value, Range("B1"), Range("C3").Value, Range("C4").Value, Range("E" & SubTot).Value, Range("E" & SubTot + 1).Value, Range("E" & SubTot + 3).Value)
    End With
    srcWS.Copy
    With ActiveSheet
        .UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
        .Shapes.Range(Array("Rounded Rectangle 1")).Delete
    End With
    With ActiveWorkbook
        Application.DisplayAlerts = False
        .SaveAs Filename:="C:\Users\Eileen\Desktop\Sales Invoices\" & Range("C4").Value, FileFormat:=51
        Application.DisplayAlerts = True
        .Close False
    End With
    With srcWS
        .Range("B1,B7:B8,B11:B12,C3:C4,D7:D8,E12,F7:F8").SpecialCells(xlCellTypeConstants).ClearContents
        .Range("A16:E" & SubTot - 1).SpecialCells(xlCellTypeConstants).ClearContents
        .Range("F1").Value = .Range("F1").Value + 1
    End With
    Application.ScreenUpdating = True
End Sub

 

[Neelie]

This works perfectly thank you so much! Do I need to mark as solved anywhere?

 

[mumps]

You are very welcome. I don't think you can mark as solved on this forum.

 

[Neelie]

OK so I've tried to save the second invoice (first one saved fine) and message says run time error. Debug shows the file path in yellow

Sub SaveInvoice()
Application.ScreenUpdating = False
Dim srcWS As Worksheet, desWS As Worksheet, SubTot As Long
Set srcWS = Sheets("Sales Invoice")
Set desWS = Sheets("Sales Invoice Journal")
SubTot = srcWS.Range("D:D").Find("Subtotal", LookIn:=xlValues, lookat:=xlWhole).Row
With desWS
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 7) = Array(Range("F1").Value, Range("B1"), Range("C3").Value, Range("C4").Value, Range("E" & SubTot).Value, Range("E" & SubTot + 1).Value, Range("E" & SubTot + 3).Value)
End With
srcWS.Copy
With ActiveSheet
.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
.Shapes.Range(Array("Rounded Rectangle 1")).Delete
End With
With ActiveWorkbook
Application.DisplayAlerts = False
.SaveAs Filename:="C:\Users\Eileen\Desktop\Sales Invoices" & Range("C4").Value, FileFormat:=51
Application.DisplayAlerts = True
.Close False
End With
With srcWS
.Range("B1,B7:B8,B11:B12,C3:C4,D7:D8,E12,F7:F8").SpecialCells(xlCellTypeConstants).ClearContents
.Range("A16:E" & SubTot - 1).SpecialCells(xlCellTypeConstants).ClearContents
.Range("F1").Value = .Range("F1").Value + 1
End With
Application.ScreenUpdating = True
End Sub

 

[mumps]

Try replacing that line with this:

SaveAs Filename:="C:\Users\Eileen\Desktop\Sales Invoices\" & .Sheets("Sales Invoice").Range("C4").Value, FileFormat:=51

 

[Neelie]

Thanks mumps but problem still there 'error 104 cannot access file'

 

[Neelie]

Tried to edit but too late!
Feeling embarrassed. Code was fine I'd changed cell for Invoice name so in the code you had C4 I'd entered the name in C3. Sorry

 

[mumps]

No need to apologize....been there, done that!!!! I'm glad it worked out.