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

Question

When an invoice is saved (using the already-present Save and Print ActiveX button, VBA), it adds the number of certain goods consecutively to the customer's row in the table. I need to have this data copied to another book, called Customer Sales. 100 rows are needed to accommodate both current and potential clients. The customers are in Column A. Then, as a header, Column B:X has several elements. Therefore, every row is set aside for a customer, and every column—except column A—is set aside for things.

I need to copy the information from the invoice in the following columns: A23:A27 (items), B23:B27 (quantities); A23:A27 contains rows of various goods. Quantities of the goods are shown in the same row for item: quantity as b23:b27.

As all goods are increased based on the quantity purchased, the customer's name is in a drop-down list in A7, but I want the client to remain on the same row in the sales book.

Item-quantity is in rows A23–B23, then the same in rows A–B27. Accordingly, the customer (A7 in the invoice) will be matched to column A in sales, and the A23 item plus B23 amount from the invoice will go to sales column B:X, depending on which item it is. The quantity increases the cell in these columns.

So I have no idea how to put this into practice. If the client purchases 10 apples, for example, the apple column's total should be increased by the number of apples purchased in the customer's row (based on cells in the invoice). I don't know much about code, but I can do it if I know what I'm trying to do, if that makes sense. What approach is the most effective for this?

Answer 1

You will need to modify those to fit your specific setup since I am unaware of the names of your workbooks or worksheets.

In your invoicing workbook, paste the following code inside an ordinary (not class) module. Put a call to this macro using the command "Call salesBookUpdate" at the conclusion of your pdf button macro (but before the "End Sub").

It will open your sales book if it is not already open. Your customer row will be located on the salesbook. If the salesbook does not contain the customer's name from invoice A7, a new row will be created for him at the first open spot.

Modify this code as per your setup:

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