0

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).

Invoice: invoice

Log: 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.

2
  • Welcome to Super User! Please note that Super User is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
    – DavidPostill
    Aug 24, 2016 at 19:17
  • 0 down vote accept The duplicate has been removed. I added more info to my question. If anyone can tell me a better code to use versus the one I have please let me know. I am open to any suggestions. Thank you,
    – A. Gipson
    Aug 25, 2016 at 21:45

2 Answers 2

0

You have invoicenumber declared and also you try to get its data, but you store the value in invoiceno.

Insert Option Explicit at the beginning of your module, it will help preventing these mistakes.

2
  • Thank you. Do you know what code I can use to have range E25:E53 (names only) populate in one cell separated by a semicolon?
    – A. Gipson
    Aug 27, 2016 at 11:24
  • That is a different question. Aug 29, 2016 at 8:25
0

This example can be reviewed for the example of the advanced invoice that made with excel userform :https://youtu.be/Qr-4of-38DI

In this invoice template ,the userforms that contains textbox,listbox and buttons are used to enter quickly data of customers and products. Ago,requested data can be searched in list through the text boxes . Then, when the list item is double-clicked or when the enter key is pressed, the item's data is entered in the sheet The created invoice can be copied to selected record sheet.

1
  • Can you include the relevant parts from the video here?
    – bertieb
    Mar 8, 2017 at 17:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .