I am using Excel 2013 to create a VBA code that will allow me to transfer data from one workbook (invoice template from Excel) to another workbook (Invoice Log).
I need certain cells from the invoice to be transferred over to a log. For example:
L4 (includes the invoice number)
L13 (includes the date of the invoice)
L58 (includes the total amount of the invoice)
E13 (Billed to Name of Site)
E25:E53 (includes patient names).
In the log, I would like:
L4=A2
L13=B2
L58=C2
E13=D2
E25:E53=E2
and I want the count of names in range E25:E53 to appear in cell F2 of the log. F2 with be the quantity.
I want cell range E25:53 (patient names only) to be combined into one cell separated by a semicolon for example, "John Doe; Mary Ann; Sam Cook".
Also, I would like to start on a new row every time new data is entered into the invoice. The first row of the Log will have the headers. See the below link to access screenshots for examples.
(I am unable to attach actual the Excel workbook. If you know a better way to do this please let me know).
The code that I currently have is:
Sub Button3_Click()
Dim InvoiceNumber As Integer
Dim InvoiceDate As Date
Dim TotalAmt As Currency
Dim BillTO As String
Dim InmateNames As String
Dim Qty As Integer
Worksheets("Invoice").Select
InvoiceNo = Range("L4")
InvoiceDate = Range("L13")
TotalAmt = Range("L58")
BillTO = Range("E13")
Description = Range("E25:E53")
Worksheets("Invoice Log").Select
Worksheets("Invoice Log").Range("A1").Select
If Worksheets("Invoice Log").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Invoice Log").Range("A1").End(x1Down).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = InvoiceNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = InvoiceDate
For some reason the code is starting with "0" as my invoice#. The invoice date, Total Amt, Billed TO, and QTY columns are working fine. However, and the patient names are not populating.