Automated Invoice Register

[Lithene]

Hi There,

Just need a more customized version of the vba from MrExcel-"Create An Invoice Register Podcast #1808".
This VBA tabulates data from an invoice to a separate worksheet. I.e. It pulls out information like Date, Invoice No, Customer Name and Invoice Amount an tabulates on another sheet.

Sub PostToRegister()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("Invoice")
    Set WS2 = Worksheets("Register")
    ' Figure out which row is the next row
    NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    ' Write the important values to register
    WS2.Cells(NextRow, 1).Resize(1, 7).Value = Array(WS1.Range("C4"), WS1.Range("C5"), _
        WS1.Range("A10"), Range("InvoiceTotal"))
End Sub

where C4 is Date, C5 is Invoice NO., A10 is Customer and InvoiceTotal is Total.

This VBA records information from the invoice to a table like below:

Date

Invoice NO.

Customer

Total

<tbody>
</tbody>

But I need the VBA to sort information into a table as below:

Date

Invoice NO.

Customer

Non-Taxable Amount

Taxable Amount

Tax

Total Amount

<tbody>
</tbody>

When customers are taxed, the InvoiceTotal is placed into Non-Taxable Amount Column, with the Taxable Amt and Taxes column empty. If the customer is not taxed, the Taxable amount and Taxes is recorded accordingly, with the Non-Taxable amount column empty. The total amount would simply be either the Non-Taxable Amount alone, or the Taxable Amount + Tax.

Is there a VBA that can sort cell values?

Much Thanks!

 

[Lithene]

After some research and looking around google, found my answer! Hopefully, it can help anyone directed to this thread

Sub PostToRegister()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("Invoice")
    Set WS2 = Worksheets("Register")
    ' Figure out which row is the next row in register
    NextRow = WS2.Cells(Rows.Count, 2).End(xlUp).Row + 1
    
    ' M21 is Invoice, N21 is Date, E12 is Company Name, N47 is GST
    
    ' Register values if No GST
    If Range("N47").Value = 0 And Range("N45").Value > 0 Then
        WS2.Cells(NextRow, 2).Resize(1, 7).Value = Array(WS1.Range("M21"), WS1.Range("N21"), _
        WS1.Range("E12"), WS1.Range("InvoiceTotal"), 0, 0, WS1.Range("InvoiceTotal"))
    ' Register values if theres GST
        Else
        WS2.Cells(NextRow, 2).Resize(1, 7).Value = Array(WS1.Range("M21"), WS1.Range("N21"), _
        WS1.Range("E12"), 0, WS1.Range("Subtotal"), WS1.Range("N47"), WS1.Range("InvoiceTotal"), WS1.Range("InvoiceTotal"))
    End If
End Sub

Sometimes the values recorded may be erratic eg. for Dates and Amounts.
You can adjust this on the excel sheet by changing the number format on the Register Sheet to Accounting or Date.