Copy Invoice Total to correlating customers Account Balance

Question

I am creating a new invoicing system for my small business.

I need a button that copies the current invoice total from a Master Invoice workbook to the Account Balances workbook under the corresponding Customer Name and Term Name.

In the Master Invoice workbook I have the Customer Name (Cell G10), what School Term it relates to (Cell H2) and the Total Invoice Amount.

In the Enrolment Database workbook I have a sheet called Account Balances. Column A contains each Customer Name and row 1 contains the School Term Names (Term 1 Invoice, Term 2 Invoice, etc.).

When the "Add to Account Balance" button is pressed I want Excel to find the current Customer Name and Term (x) Invoice in the Master Invoice and copy the Invoice Total to corresponding Cell in the Enrolment Database workbook.

Example: TEST TESTINGTON's Term 1 Invoice comes to $90. When I press my button it copies that $90 and pastes it in Cell B3 because that is the intersection of TEST TESTINGTON (Customer Name) and Term 1 Invoice (Term Name)

Master Invoice notes

Enrollment Database notes

Here is the latest code but I've never got it remotely working

Private Sub AddToAccounts_Click()  
    Workbooks("Master Invoice.xlsm").Worksheets("Master Invoice").Range("j40").Copy
    Workbooks("Enrolment Database.xlsm").Worksheets("Account Balances").Range(Cells("A2:A150", "B1:E1").Address).PasteSpecial Paste:=xlPasteValues  
End Sub

Please include your code attempt in your question. – braX Dec 10 '19 at 14:08 — braX, Dec 10 '19 at 14:08

PeterT · Accepted Answer · 2019-12-11T14:03:55.190

Here's an example of how you can copy the invoice data to your balances sheet. The important things in this example are:

Always use Option Explicit
Declare and use intermediate variables. This greatly helps to focus your work on exactly which workbook, worksheet, and information you're focused on for that part of the code logic. As an example, define both your current workbook and the enrollment workbooks separately. This way you don't have to continually work with a long hierarchy of references.

So don't do this:

Workbooks("Master Invoice.xlsm").Worksheets("Master Invoice").Range("j40").Copy
Workbooks("Enrolment Database.xlsm").Worksheets("Account Balances").Range(Cells("A2:A150", "B1:E1").Address).PasteSpecial Paste:=xlPasteValues

Do this:

Dim invoiceWS As Worksheet
Set invoiceWS = ThisWorkbook.Sheets("Master Invoice")

Dim enrollmentWB As Workbook
Dim balancesWS As Worksheet
Set enrollmentWB = Workbooks.Open("Enrollment Database.xlsm")
Set balancesWS = enrollmentWB.Sheets("Account Balances")

Here is the whole example as a single sub. Notice the error checking to keep your code from crashing and how the variable names self-document the code logic.

Option Explicit

Sub AddToAccounts_Click()
    Dim invoiceWS As Worksheet
    Set invoiceWS = ThisWorkbook.Sheets("Master Invoice")

    '--- collect the data on the current invoice
    Dim customer As String
    Dim currentInvoice As Double
    Dim currentTerm As String
    With invoiceWS
        customer = .Range("G10").Value
        currentTerm = .Range("H1").Value
        currentInvoice = .Range("J40").Value
    End With

    Dim enrollmentWB As Workbook
    Dim balancesWS As Worksheet
    Dim enrollmentWBWasOpen As Boolean
    Set enrollmentWB = ThisWorkbook
    'Set enrollmentWB = GetWorkbook("C:\Temp\Enrollment Database.xlsm", _
                                    enrollmentWBWasOpen)
    Set balancesWS = enrollmentWB.Sheets("Account Balances")

    '--- find the row with that holds the current customer's invoices
    Dim customerBalances As Range
    Set customerBalances = balancesWS.Range("A:A").Find(What:=customer)
    If customerBalances Is Nothing Then
        MsgBox "ERROR: Customer not found! (" & customer & ")"
        '--- optional??
        If Not enrollmentWBWasOpen Then
            enrollmentWB.Close
        End If
        Exit Sub
    End If

    '--- now find the column that matches the term
    Dim term As Range
    Set term = balancesWS.Range("1:1").Find(What:=currentTerm)
    If term Is Nothing Then
        MsgBox "ERROR: Current term not found! (" & currentTerm & ")"
        '--- optional??
        If Not enrollmentWBWasOpen Then
            enrollmentWB.Close
        End If
        Exit Sub
    End If

    '--- copy the invoice value
    balancesWS.Cells(customerBalances.Row, term.Column).Value = currentInvoice

    If Not enrollmentWBWasOpen Then
        enrollmentWB.Close
    End If

End Sub

Public Function GetWorkbook(ByVal sFullName As String, _
                            Optional ByRef wasAlreadyOpen As Boolean) As Workbook
    '--- credit to: https://stackoverflow.com/a/9382034/4717755
    Dim sFile As String
    Dim wbReturn As Workbook

    sFile = Dir(sFullName)

    On Error Resume Next
        Set wbReturn = Workbooks(sFile)

        If wbReturn Is Nothing Then
            Set wbReturn = Workbooks.Open(sFullName)
            wasAlreadyOpen = False
        Else
            wasAlreadyOpen = True
        End If
    On Error GoTo 0

    Set GetWorkbook = wbReturn

End Function

Thank you so much for this @PeterT! However, I'm getting a "Run-time Error - 9 - Subscript out of range" when setting "balancesWB": Set balancesWS = enrolmentWB.Sheets("Account Balances") I have literally copy-pasted the sheet name into the brackets to no avail :( — Dan B, Dec 10 '19 at 16:02
I would try to rename the sheet itself to something ("`junk`" maybe), then rename it again back to "`Account Balances`". Smells like there's a hidden character somewhere. Also, I'm assuming you typed in your comment by hand rather than copy-pasting from your code. In my example there is no `balancesWB` and your `enrolmentWB` name is mis-spelled (should have two lowercase "L"s) — PeterT, Dec 10 '19 at 17:05
My sincerest apologies PeterT, I must have been tired last night... Your code was perfect. Firstly I didn't enter the specific path to "Enrollment Database.xlsm" and secondly I gave you the wrong Cell reference for Term Name, it should have been H2. Could I ask one more thing, if I already have the Enrollment Database.xlsm workbook open how do I reference it without running the 'Workbooks.Open()' code? Cheers in advance. — Dan B, Dec 11 '19 at 13:06
I added code from my own library based on [this answer](https://stackoverflow.com/a/9382034/4717755) that, given a full path to a workbook, will either open the workbook or return the currently open workbook. There is an optional `ByRef` parameter you can check to determine if it was already open, so you can conditionally close it when you're finished. — PeterT, Dec 11 '19 at 14:06

Copy Invoice Total to correlating customers Account Balance

1 Answers1