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

0 votes
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?
Oct 2, 2022 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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
        Set salesBook = Workbooks.Open("C:\Temp\salesBook.xlsx") ' change directory and filename to yours
    End If


    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, _

With salesSheet.Range("a1:a" & lastRow)
    Set c = .Find(custID, LookIn:=xlValues, MatchCase:=False)
    If Not c Is Nothing Then
            salesBookRow = c.Row
        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
answered Oct 3, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Using Visual Basic to pull data from within a range to use in an Excel function

Use AVERAGEIFS instead of the full range. ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How To Copy/Cut Row of Data Based on TRUE/FALSE Condition [Excel VBA]

Solution Loop through the rows on the Price ...READ MORE

answered Feb 4, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

How to multi level sort an array in excel, using formulas? I am aware about the way, using SORT button on DATA tab

Use SORTBY, e.g. =SORTBY(A2:B5,A2:A5,1,B2:B5,1) Or simply&nb ...READ MORE

answered Jan 22, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Removing specific rows in an Excel file using Azure Data Factory

Under the 'Source' tab, choose the number ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP