Using an excel invoice template to consecutively add items to sales data book, keeping individual customers on their own specific row

Question

I need to have certain data copied to another book, called Customer Sales, which every time an invoice is saved (using the already present Save and Print activeX button, vba), it adds the quantity of certain items consecutively to the customers row in the table. So 100 rows, to accommodate existing and new customers. Column A is the customers. Then from Column B:X is different items as a header. So each row is reserved for a customer, whereas each column, except A, is reserved for items.

The data from the invoice i need copied is A23:A27 (items), B23:B27 (Quantities); data in a23:a27 is rows of different items. The b23:b27 is quantities of those items, same row for item:quantity.

The customers name is in a drop down list in A7, but I want the customer to stay on the same row in the sales book, so all items are increased based on quantity bought.

the A23-B23 is item-quantity, then same for rows to A,B27. So on the sales log, it'll be Customer (A7 in invoice) matched to column A in sales, then A23 item plus B23 quantity in invoice goes to Sales column B:X depending on which item, and the quantity increases the cell in these columns.

So I have no idea how to implement this. Maybe an If/Else statement, like if customer buys 10 apples increase total of apple column by quantity bought on customers row (based on cells in the invoice).

I have very little knowledge of coding, but can do it when I know what it is I am trying to do if that makes sense...What is the best method of doing this?

Thanks in advance!

That is much clearer. Except for this - does each customer have 100 rows in a table on this sheet? And what are in columns B through X of the sheet you are writing to. Is each column a sales item? — John Muggins

John Muggins John Muggins · Accepted Answer · 2017-03-22T13:24:01

Now your question involves everything we need to know.

I don't know your workbook names or worksheets that you are using, so you will have to change those to suit your particular set up.

Paste the following code into a regular (not class) module of your invoice workbook. At the end of your pdf button macro (but before the "End Sub") put a call to this macro by the command "Call salesBookUpdate."

If your sales book is not open it will open it. It will find your customer row on the salesbook. If the customer name from invoice A7 is not found in the salesbook, it will create a new row for him at the first unused row. It will then look up the items he purchased and add them to any existing previous purchases of the same object according to column.

It might take some tweaking to work on your particular set up.

Sub salesBookUpdate()
Dim custID As String
Dim item1() As Variant
Dim wbInvoice As Workbook
Dim salesBook As Workbook
Dim salesSheet As Worksheet
Dim lastRow As Long
Dim salesBookRow As Long
Dim colrange As Range
Dim columnNumber As Long

    Set wbInvoice = ThisWorkbook
    Sheet1.Activate                         ' change to your correct data sheet
    item1 = Range("A23:B27")
    custID = UCase(Sheet1.Range("A7").Value)
    If IsFileOpen("C:\Temp\salesBook.xlsx") Then
        Set salesBook = Workbooks("salesBook.xlsx"): salesBook.Activate
    Else
        Set salesBook = Workbooks.Open("C:\Temp\salesBook.xlsx") ' change directory and filename to yours
    End If

    salesBook.Activate

    Set salesSheet = salesBook.Sheets("Sales Sheet")                           ' change worksheet to correct one

    lastRow = salesSheet.Cells.Find(What:="*", _
        After:=Range("A1"), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row


With salesSheet.Range("a1:a" & lastRow)
    Set c = .Find(custID, LookIn:=xlValues, MatchCase:=False)
    If Not c Is Nothing Then
            salesBookRow = c.Row
    Else
        salesBookRow = lastRow + 1
        salesSheet.Range("A" & salesBookRow).Value = custID ' new customer
    End If
End With

Set colrange = salesSheet.Range("B1:X1")
For i = LBound(item1) To UBound(item1)
    If item1(i, 1) <> "" Then
        With colrange
            Set c = .Find(item1(i, 1), LookIn:=xlValues, MatchCase:=False)
            If Not c Is Nothing Then
                    columnNumber = c.Column
                    Cells(salesBookRow, columnNumber).Value = Cells(salesBookRow, columnNumber).Value + item1(i, 2)
            End If
        End With

    End If
Next i



End Sub

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Using an excel invoice template to consecutively add items to sales data book, keeping individual customers on their own specific row

1 Answers