Macro to add value from input sheet into another sheet

0

 I have created an invoice input sheet. I have a macro running to add the information from the input sheet under new rows to an invoice tab when I press the submit button. However I would also like to be able to submit the input sheet and it matches the reference to a reference on my sales sheet then updates the total amount invoiced for the corresponding reference number. i.e. if there is £1000 already under ref 1234, when I add another invoice against ref 1234 for £1000, the sales sheet will update to have £2000 rather than overtyping the figure. Is this something that is possible?

Answer
Discuss

Discussion

Hi Dpat and welcome to the Forum

That sounds achievable (and the ease of doing so will be subject to the form of your file). It would be helpful if you could edit your original Question and use the "Add files..." button to mupload a representative Excel file for us to look at (and provide an appropriate solution).
John_Ru (rep: 792) Feb 22, '21 at 3:54 pm
Database Rule #1 = Never over-write data after they are entered.
For your new system you need 4 sheets. One, customer names and addresses (database). Two, Item number, descriptions and prices (database). Three, a template for preparing an incoice (template). Four, a list of issued invoices (database), What you are talking about is a fifth sheet: Sales per customer (Report). That is easy to create from the Invoices database in several ways. But if you follow your plan you will only have the sales report and no more invoices database. That's a step backward because to find details of past invoices you will have to refer to paper copies since your db has been converted to a report.
In 3 words: Don't do it. :-)
Variatus (rep: 4402) Feb 22, '21 at 7:09 pm
Hi John_Ru. I have added an example sheet. I have a macro running that works (this adds the input form data as a list to the invoice tab). I have a second macro that's not running and doesn't work (which includes the code for the working macro and the extra bit I would like it to do), which hopefully shows what I am trying to do.
dpat1234 Feb 23, '21 at 4:36 am
Add to Discussion

Answers

0

Dpat

Please find attached my version of your file- in that I've modified your Module2 and assigned that to the Submit button on the Add Invoice sheet (from where it should be run).

The key changes are in bold in the listing below. Firstly I've defined a new variable q_ref (for use later) then moved your working code to the start of the macro.

I note that your macro does NOT disobey the database guidance provided by @Variatus under the Discussion (since a new line is added each time to the Invoiced sheet). You'll see your "working" code is now in a With / End With statement (purely to reduce the amount of text) but the dots are important (so .Cells refers to the sheet stated in the With but Range does not).

Once that sheet has been done, I add a new With (for the sales funnel, a snapshot in which you're just looking for what's coming, balance left to bill etc.). Instead of your Find, I've looped through used cells in column B and (if a cell matches  q_ref), that row is the one to modify for values. If it's not found, a message alerts the user (and the # around q_ref will show up and unwanted spaces around it).

I then modify the Total Invoice and Balance Due values to allow for the new invoice value. Also, if that line isn't already showing Won, the macro changes it to that status.

Here's the macro code:

Sub add_invoice1()

'Define sheets as variables
invoice_output = "invoiced"
funnel_output = "sales funnel"
q_ref = Range("quote_ref").Value

With Sheets(invoice_output)
    'Adds new row
    next_row = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    'Adds data from form into new row
    .Cells(next_row, 1).Value = Range("invoice_date").Value
    .Cells(next_row, 2).Value = Range("quote_ref").Value
    .Cells(next_row, 3).Value = Range("install_date").Value
    .Cells(next_row, 4).Value = Range("project_name").Value
    .Cells(next_row, 5).Value = Range("invoice_amount").Value
    .Cells(next_row, 6).Value = Range("p.o.").Value
End With

' Modify funnel, if possible
With Sheets(funnel_output)

    'Find the quote ref given in the form, in funnel tab
    For r = 7 To .Cells(Rows.Count, 2).End(xlUp).Row ' loop from row 7 to last used row in B
        If .Cells(r, 2).Value = q_ref Then
         row_quote = r
        End If
    Next r

    ' say if quote_ref not found
    If row_quote = "" Then
        MsgBox "Updated " & invoice_output & " but #" & q_ref & "# not found in " & funnel_output
        Exit Sub ' skip revision to funnel
    End If


    'Get the cell with the existing quote value (column #7)
    existing_invoice_value = .Cells(row_quote, 7).Value

    'Get the new invoiced value from the form ## different
    new_invoice_value = Range("invoice_amount").Value

    'Replace the invoice values on the funnel tab, with the new value added just above
    If .Cells(row_quote, 5) <> "Won" Then .Cells(row_quote, 5) = "Won" 'correct cell (since invoice raised)
    .Cells(row_quote, 7).Value = existing_invoice_value + new_invoice_value
    .Cells(row_quote, 8).Value = .Cells(row_quote, 8) - new_invoice_value ' adjust balance

End With

MsgBox "Updated " & invoice_output & " sheet for " & q_ref & " and revised " & funnel_output

End Sub
You might want to clear the values in the Add Invoice sheet before that message (or at least the invoice value) but that's your choice.

Hope this works for you.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login