Learn Excel – Create an Invoice Register – Podcast #1808

Learn Excel – Create an Invoice Register – Podcast #1808


MrExcel Podcast is
sponsored by Easy-XL. Learn Excel from MrExcel podcast, episode
1808 – Create an Invoice Register. Hey. Welcome back to MrExcel netcast. I’m
Bill Jelen. Well, one of my most popular videos out of YouTube is episode
1505: “Next Invoice Number”. This shows how to take a template from
Office Online and add a little bit of Macro language to increment the invoice number,
save the invoice out to its own file. We got a question a few days ago: “Thanks
so much for this video. My boss loves it. He loves it so much that he’s challenged
me to figure out a way to populate a master spreadsheet with each invoice’s
information automatically after it’s saved” – we’re actually going to do it before it’s
saved, or in the process of saving it – “I thought it would be
easy – just link the cell to another cell in
another spreadsheet, but then it occurred to me that
each invoice is a separate sheet, so this information would just continue
to update over the old information. Any awesome way to do this?”
Yes! Absolutely, there’s an awesome
way to do this. So, here we go. Microsoft Excel, “File”, “New”. We’re
going to search for “Invoices”. “Sales Invoices”. Alright, so, here’s the invoice. We’re going to go back and add
the same code that we added to the Episode 1505, but let’s talk
about how to do an Invoice Register. First thing, “Simple Invoice” – I hate
that – let’s just call this “Invoice”, or something like that.
Get rid of the space. We’re going to add a new sheet called
“Register”, as in “Invoice Register”. So, this is going to be our Invoice
Register, and we have to think about what fields our manager wants.
He probably wants “Date”, “Invoice Number”, “Customer”,
and “Amount”. At least that much. There might be more fields. So, if you have
more fields, that’s okay, we’re going to have to just add the same logic that
we’re going to do for these fields. Alright, so, we have our new sheet called
“Register”. We have our new renamed sheet called “Invoice”. This is out
there in Compatibility Mode, so we have to do “File”,
“Save As”. And it has to be a Macro-Enabled
Workbook, so make sure to change that. And we’re just going to
call it “InvoiceProgram”. Alright, good. Now, we’re going to make
a few notes here. I have a notepad – you can’t see the notepad, it’s an actual,
physical post-it note – in my hand. I’m going to know that Invoice Number
is in cell C5. C5 is Invoice Number. The Invoice Date is in C4. The Customer,
I’m going to use cell A10, and, let’s see… the Total.
Alright, now, maybe they’re going to insert new
lines, so I’m going to come here to this “Total” cell, and I’m going
to just call it “Invoice Total”… “InvTot”, it has to be
one word, no space. So the range name “InvTot”
is our Total Sales. So those are the 4 fields that we want
to capture in the Invoice Register. Alright. We’re going to press
Alt+F11 to switch over to VBA. We’ll do “Insert”, “Module”.
Good. And I’m going to add in the code from
episode 1505, hang on. Okay, so, there you go, I
actually just typed that code, being careful
that in the other video (which you have to watch to
get the explanation of this), it said to make sure
to customize the cell that has the Invoice Number.
In the old video that was cell E5. In this particular template that I
downloaded today, it’s C5. Alright, so, now, we’re going to add
some new code up here. I’m going to call this
“Sub PostToRegister”. And we’re going to “Dim WS1 As
Worksheet”, “Dim WS2 As Worksheet”, “Set WS1=Worksheets”. I call it “Invoice” (if you can’t remember
that, it’s right over here in the Project Explorer). And
“Set WS2=Worksheets (“Register”)”. Alright, those have to match. Now, first thing we’re going to do: I’m
going to add some comments here: “ ‘ Figure out which
row is the next row”, and then “ ‘ Write the
important values to Register”. Alright: “Figure out which row is the next
row”. First off, we have to make sure that we are using a field that
will always be filled in. No blanks, so, probably “Date” or
“Inv #” are really a safe one to go. So “Date” is in Column A,
that’s Column 1. Alright, so, “NextRow=WS2.Cells
(Rows.Count, 1)”. Right there, that “1” – if your Column A
has a field that is not always filled in, like “Salutation”, or “Apartment Number”,
don’t use Column 1, use some other column that’s always going to be filled in.
“.End(xlUp).Row + 1”. Alright, so, this will tell us where
we should write this Invoice to. And I’m just going to test it, so let’s
do a little message box here, “NextRow”. Just to make sure. So, we have Title in Row 1, blank
in Row 2, Headings in Row 3. The answer here better be a “4”.
Alright, so we’ll just run this,
click the “Run” button, and, sure enough, we get a 4, alright?
So, that’s good, that means that
that code is working. I can get rid of
the message box. And then what we’re going to
do is we’re going to say: “WS2” – that’s our Register worksheet
– “.Cells(NextRow, 1).Resize” 1 Row, 4 Columns. Now, if you’re collecting
more information than the 4 columns than I’m collecting – which I’m sure you
are – that number is going to be bigger than 4, it’s going to be 5,
6, 7, 8, 9, whatever it is, “.Value=Array”, and then, we’re going
to put the 4 values that we want. The 4 values, so in my case it’s Date
first, so that’s “(WS1.Range(“C4”)”. I’m getting that from the notepad
that I wrote that down on. And then “WS1.Range(“C5”)”
is the Invoice Number. And then we need the Customer.
The Customer is coming from A10. So, we’re going
to go off the screen here, I’m going to put the continuation character
– that’s a space and an underscore – and go down to the next line.
“WS1.Range(“A10”)”, and then, finally, the Range
called “Invoice Total”. And we will close the
Array. Alright, so, what this tiny little Macro does
is, it figures out where the next row is, and then it writes all the information
from this invoice onto that row. Alright, so, here is a test. Let’s come
back and we will have our Invoice 12345, today’s date, and the customer is
going to be “Joe’s Computer Books”. And we’re going to sell them 10
copies of “Slaying Excel Dragons” for $299.95, and 15 copies of
“Don’t Fear the Spreadsheet”. Let’s see, that’s equal
15 times $14.95. And they should buy one copy of
“VBA and Macros for Excel 2013”. If you’re watching this and you want to
learn Macros, that’s a great book, $39.95. Alright, we’ll just scroll down here
and see what our total is, make sure that we’ve got it
right, $564.15. Alright, let’s run our code, I’ll come
back here, click inside the Macro, click “Run”, and when I go look at the
Register, we should see that we have the date, the invoice, the customer, and
the amount. Alright, the date needs to be formatted as a date. Alright, but it looks good. And if we
would happen to run that Macro again, (which, in real life, we
would never run it twice), “PostToRegister”, see it’s going
to the next row. That’s awesome! Okay, so, we take this code, “PostToRegister”,
and when do we want to run it? We have 2 Macros already,
“SaveWithNewName”, that makes a copy of the
workbook to a new place, and then it calls “NextInvoice”. Well,
right here, before we make that copy, that’s when we want to “PostToRegister”.
PostToRegister… There we go. Alright, so, here’s our old Macro
that was calling “NextInvoice”, now it’s going to call “PostToRegister”,
so we’re going to write the things to the Register, we’re going to make a copy
and save it, and then increment the Invoice by 1, and we
will be all set to go. So, if you’re watching this video,
you want to go back and watch, first, this video from a while ago: Episode
1505, that will show you how to set up this code and explain this code. And
then to add the Invoice Register, just add in this new, tiny, little Macro
here, because I’m only collecting 4 cells, in real life you certainly would
be collecting more cells. Now, hey, I have to caution you.
When I started to think about this, if you need to gather
all of the items here, that really needs to be
saved to a new sheet, like a “Line Item Detail Register
Sheet” or something like that. Don’t try and put this on the Register
Sheet. The Register Sheet just wants to have one line per
invoice with your totals. If you need the line item details,
then put that on a second sheet. You can join those back
together using PowerPivot or a lot of VLookups.
Your call. [music] Hey. I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.

100 thoughts on “Learn Excel – Create an Invoice Register – Podcast #1808

  1. Hi Mr Excel – as always – excellent! THank you!
    Any chance you could do the extension of this video with the new sheet with all the details of the invoice and then a sheet to show how it merges – that part is easier than the macro part – that as I said previously is still new to me!
    Your books are excellent by the way – purchased all of them – busy reading them – thank you!

  2. hello Bill, 
    great job !!  Instead of saving the invoice in a different folder. how can I save it in same worksheet after my register sheet.
    I have my tabs as follow… Partsbase quote sheet then register sheet. I would like to add my invoices in same worksheet.
    also I added the save pdf function and I don't get an error, but just don't do it. 

  3. Hello Bill,

    Thank you very much for your Pod casts. They are helping a lot. 
    I have one question on this lesson. How shall modify the Macro if I would like to see the description section in the Register sheet ? As it has 3 different lines I would like to create a report showing each description in different rows with the same invoice number. Appreciate if you can help me.

    Thanks. 
    Siyon

  4. I am using both Podcast 1505 & 1808 and I love it. Now I need help on creating a register that will track the items that are sold on the invoices. Can you help?
    Thanks for all the podcasts.

  5. Hello Bill

    First of all, THANK YOU! I used the Podcast 1505 and it worked perfectly!! I am trying to use this one. However, I couldn't get through it! I wrote the code just like you wrote it, changing of course the names of the worksheets, and it says Compile Error.

    This is what i wrote down:

    Sub PostToRegister()
        Dim WS1 As Worksheet
        Dim WS2 As Worksheet
        Set WS1 = Worksheets("FORMATO")
        Set WS2 = Worksheets("REGISTRO")
        
        'FIGURE OUT WHICH ROW IS THE NEXT ROW
        NextRow = WS2.Cells(Rows.Count, 1) .End(xlUp) .Row + 1

    This last sentence lights up in red. I am new at this. Could you please help?

    Thanx so much!!!

  6. Thank You Bill! I have used 1505 & 1808 to add the counter, save format, registry, and picked up a few more ideas along the way. It would have taken me much longer to write this new Quotation Form and Register to replace my bosses old Word Document Template and manual file save method. Saving him time and me the extra efforts to retrieve basic info. Thank you! 

  7. Hello
    I wrotethe code exactly the way in you video , it worked but not completly , the part of Epesoid 1055 worked  very well , after adding the code of this epsoid spread sheets will be stored but with out the content of the table ! please may you help me ?

  8. Hey Bill,

    I have watched podcast 1505 as well as 1808. I just have a quick question. Can you write a code in the register that will generate an invoice? I guess what I mean is basically doing the same exact thing as you did to the invoice that made information go to the register but instead have information go from the register to the invoice. I have around 400 constant customers and it would be easier to be able to input the records on the register. I am sorry if I am unclear! I am still very new to Excel.

  9. Hey Bill,
    I have watched 1505 and 1808, both are very helpful, you are great. I just have question if you can help me, about the ActiveWorkbook.SaveAs it will still the same if I want to use it between different computers like small network?
    Elias

  10. Hi Bill, the saved sheet from your podcast 1808 is NOT allowing drop-down list (Data Validation) to work.
    I desperately require the ability to add / change items from the drop-down list to work in the saved sheet.

  11. Hi Bill 

    Thank you so much for all the info 
    I created My Invoice and register
    My life is uncomplicated now… Please can you help with a code for The program to print 2 copies of my invoice before it saves writes to register and closes

    Kind Regards

    Debra

  12. bill you are great help. created the macro all is working, except this little hook up.i put all function in one button save to file( pdf + xls ), change invoice number and then print.
    the problem i`m having is when closing excel we sometimes forget to answer ( yes ) to save file. then we overwire old invoice number

    what can i do.

    thanks

    Sub NextInvoice()
        Range("e5").Value = Range("e5").Value + 1
        Range("a13:a38").ClearContents
        Range("d13:d38").ClearContents
       
    End Sub
    Sub SaveInvWithNewName()
        Dim NewFN As Variant
        ' copy invoice to a new workbook
        ActiveSheet.Copy
        NewFN = "c:INVOICEInv" & Range("e5").Value & ".xls"
        ActiveWorkbook.SaveAs NewFN
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN
        ActiveWorkbook.PrintOut copies:=1
        Range("e5").Value = Range("e5").Value + 1
        Range("a13:a38").ClearContents
        Range("d13:d38").ClearContents
        ActiveWorkbook.Close
        NextInvoice
       
    End Sub

  13. Hi Bill,
    thank you for all your help on invoice & register. would you mind helping me with the same invoice & register thing but this time I need it for the whole workbook that consists of a few sheets with different invoice templates to synchronise so that the invoice number will not overlapped. Please.. Thank u

  14. This is great, but I seem to be having an issue. I am using Excel 2010, and when I run the Macro I am getting error code 400…Everything appears to work, I get the saved invoice in the designated location, the cleared invoice w/ new number, and info posted to the register. So why the error?

  15. Bill I took a look at my code after you said it was a catch all error and was able to figure it out! Thank you

  16. Hey Bill, These pod casts are great – thank you.
    I have a question and I hope you can resolve: I want to save and send SOME invoices via email OR Save and print the invoices that are not emailed. The invoices need to be saved as PDF's. Also, the invoices are saved to a cloud and often the email client will change (so it will not always be Outlook). I can insert code to save and send via outlook but don't know how to save and send using a chosen client OR save and print if not emailed. Please help? 🙂 (oh, and I'm also keeping an invoice register using your tutorial and wanting to use VLOOKUP to populate the invoice based on customer number)

  17. NEED HELP AGAIN Bill 🙂  file work really good. made some more mods all seems to work good. right now i have it saving 2 types of files. excel and PDF. what code do i remouve to eliminate the Excel. all i need to store is the pdf file.thanks

  18. Hi Bill Jelen Podcast 1805 and 1808 are the best I can expect here online. My problem is little different from this. I don't want my data in the next empty cell. I want my data in a specific cell. Like if I'm making an invoice for a club membership and a member didn't paid for last 2 or 3 months the cell for that remains blank and the data goes to the cell for current month. I will mention the month in invoice and macro will send that data to the relevant cell for that month. Sorry for my English and if I'm asking more 🙂

  19. Hi Bill, thank you for these awesome tips! but there is something i want to ask …
    Instead of saving the register list in another worksheet, i want to save it in another workbook, can u help me out on this one??

  20. You are a saint for posting these help videos! They are very helpful to me. I do have a question, I have 4 separate sheets, invoice, sales receipt, packing list and purchase order. Do you have a video that shows how to combine these four into one workbook? I've tried copy and paste but that hasn't worked for me. I expect you are a busy man, so I appreciate any help to can give.

  21. Wow this blows my mind!
    I'm so new to all this stuff.. I've only just learnt what excell is 4 month ago…
    I really need help I am an absolute beginner. Is there anything out there for me to start learning from the beggining?

  22. U are awesome!!!
    Took me a while but I finally did it thanx so much…
    Now I just need a way to do this with all my jobs.
    There must be a way to have a job register and also a running total which adds everything up… I'm getting there

  23. Dear Bill, thank you so much! I managed to use this tutorial to update one of invoices spreadsheet that i will use in my company. Great video.

  24. I HAVE ERROR 1004

    Sub posttoregister()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("SO")
    Set WS2 = Worksheets("Register")
    NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    WS2.Cells(NextRow, 1).Resize(1, 6).Value = Array(WS1.Range("H3"), WS1.Range("H4"), WS1.Range("B13"), WS1.Range("C13"), WS1.Range("H3"), WS1.Range("B6"), Range("sototsl"))

    End Sub

  25. Hi Bill
    Mr excel = easy to work with and understand
    quick question about podcast 1808
    i got everything working but instead of the register being in the same file as invoice
    is there away i can have the register in a separate workbook?

    Thanks

    Anthony

  26. sorry for distributing you at this time, but how do you adjust it in order to be completely online with the date and the time, non stop?

  27. sorry again and sorry at this time but i have figured something out, it works on any Excel type say the columns are F9 the locum you enter certain details and D7 the one that should display date or Date & time.
    you Enter this Formula on Column D7 =IF(F9<>"",IF(D7="",NOW(),D7),"") once you've entered the details on column D09 and pressed enter then it automatically enters the date & time by self

  28. Hi Bill, everything works great except for when I click my save and clear button it is deleting from cells not specified in the code. Could you look at me code and let me know why that is happening? Thank you for the great videos. Here is my code:Sub  PostToRegister()
        Dim WS1 As Worksheet
        Dim WS2 As Worksheet
        Set WS1 = Worksheets("Invoice")
        Set WS2 = Worksheets("Register")
       
        ' Figure out which row is next row
        NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
           
        ' Write the important values to register
        WS2.Cells(NextRow, 1).Resize(1, 5).Value = Array(WS1.Range("H11"), WS1.Range("C13"), WS1.Range("C14"), WS1.Range("H12"), Range("InvTot"))
    End Sub
    Sub NextInvoice()
    Range("H12").Value = Range("H12").Value + 1
    Range("B21:H30", "C13:D16").ClearContentsEnd SubSub SaveInvWithNewName()
        Dim NewFN As Variant
        ' Copy Invoice to a new workbook
        PostToRegister
        ActiveSheet.Copy
        NewFN = "C:UsersJoelDesktopLF Auto WorksInv" & Range("H12").Value & ".xlsx"
        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
        NextInvoice
       
    End Sub

  29. hi bill can you please help to hyperlink invoice number in register to open saved file with the same invoice number

  30. I want to use this to save the entire workbook. This only saves the first sheet can you please let me know how to save multiple sheets in the workbook. thanks

  31. Hi, thanks for your help, this tutorials have really help me organize my invoices better. Is there any way of gathering all the info from the invoice's description so I can substract that from the inventory that I already have, please your advice, thanks all the way from Ecuador!

  32. Can you please show if I want to create a detailed line item report so that it will have invoice number along with the line items?

  33. Hello this is a very helpful video. I have a question is it possible to have the register sheet in a separate workbook not in the same workbook? I am using this information to create a quoting form at work. After the quote is made there is other info added to keep track of so I want to have this script run later to the register. So each quote would have a button to run this script to post to register so the register would not be with the quote.

  34. Thanks Bill for this wonderful video!! I have created a button and inside that button I have written your code, however I don't know why I am keep getting this message "Runtime error 91: Object variable or with block variable not set". Could you please help me.
    My Code:
    Sub Button471_Click()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("Invoice_Org")
    Set WS1 = Worksheets("Data")

    Nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set WS2.Cells(Nextrow, 1).Resize(1, 2).Value = Array(WS1.Range("oknWhoID"), WS1.Range(oknInvoiceDate))

    End Sub

    Thanks.

  35. Dear Sir

    We maintain two different workbook for invoice and register and we maintain the register by date wise. We are a service oriented company and we put all our invoices in the register under two different categories. For example, In Register workbook sheet 1

    Cell A1: 01/01/2017( Bold and highlight)
    Cell A2: X2 All the headings of invoices we want to see in the register
    Cell A3 New invoices created today ( Bold and Highlight)
    Cell A4 : X4 Put all the information made on 1st invoice,
    Cell A5 : X5 Put all the information made on 2nd invoice
    Say Every day we made 10 new invoices. So keep gap for all those invoices. Then

    Cell A14 Payment received on today ( Bold and Highlight. As we are services oriented company so in most cases we don't receive the payment when we made the invoice. So we put all the invoices in different heading for which we received payment in a particular day).

    Cell A15 : X15 Put all the information we received first payment from any invoice ( No matter on which date it was created)
    Cell A16 : X16 Put all the information we received second payment from any invoice

    Thus we put all other invoices from which we received any payment.

    At the end of the day we close all the payment info. And then we go to next day for example

    Cell A25: 01/02/2017 ( Bold and Highlight)
    And then follow the same method we follow for 1st January except the invoice headings which we put on Cell A2: X2. For each month we only put the headings once.

    It would be a great help if you advice me the Code by which we can transfer all the data to the Register when we make the invoice or update the invoice. Pls note that we update the invoice when we receive the payment.

  36. Hello Bill,

    I have protected the invoice worksheet to preserve the formulas, but when I "save the file to new name" into another folder that file is also saved in protected mode. Is there a way to save it as unprotected? Thank you so much.

  37. hi Bill, thank you for sharing your knowledge,
    but i have something to ask,
    how if i want to record the invoice detail, like i have to list down all the things from the invoices,
    so i can records all the goods out bought by customer.
    for example there are 3 customers,
    1st bought 3 types of items
    2nd bought 5 types of items
    3rd bought 2 types of items

    so i want to records all 10 items in 10 rows, and when there is new buyers,
    macro will help me to add directly into the next rows as many as needed.

    btw im not watching all of your videos yet, but is there any video that already explained it?

    thanks.

  38. Hi Bill, Thanks for the great videos, All videos helped me perfectly,

    Can you please write down the formula which can help me with my income statement so i can calculate my income tax?

    I am using a income statement template downloaded from office.com which has 12 columns of Months and 13th of YTD and income tax row and net income row at the bottom for each month and for YTD. I am currently using excel 2010.

    Income Tax card Is below:

    Taxable Income Rate of Tax

    where income does not exceed 400,000 0%
    400,000 but does not exceed 500,000 7% of the amount exceeding 400,000
    500,000 but does not exceed 750,000 7000+10% of the amount exceeding 500,000
    750,000 but does not exceed 15,00,000 32000+15% of the amount exceeding 750,000
    15,00,000 but does not exceed 25,00,000 144,500+20% of the amount exceeding 15,00,000
    25,00,000 but does not exceed 40,00,000 344,500+25% of the amount exceeding 25,00,000
    40,00,000 but does not exceed 60,00,000 719,400+30% of the amount exceeding 40,00,000
    where taxable income exceed 60,00,000 1319,500+35% of the amount exceeding 60,00,000

    reply will be highly Appreciated. Thank you

  39. Hi Bill, Is there a way to prevent a userform entry from deleting spreadsheet cell formulas when entering data to that cell
    I'm revisiting excel after 12 years and cant remember what to do. Can you please help?

  40. Hi Bill, amazing work here. I am a contractor and I want to create a workbook to track my materials. All of the inventory tutorials are for people selling items on an invoice. I need it to be a sheet that is filled out when stock items are used on a job site or new stock is purchased for a job. This way I can count what is used each day and fill that out on a sheet like the one you have here as "invoice" and have it populate to a "register" with line item details. Do you have any videos that will help with my unique situation?

  41. hey Bill, my invoice has been working flawlessly, however I created another one for another company and used the same code, minus a few changes in rows and columns. but for some reason it keeps crashing excel. I can't seem to figure out why it's crashing. I have the save to PDF and Excel to both computer and Dropbox code inputted as well.

  42. hello Sir
    i have two quastion
    no. 1
    i make one invoice example 10 item different type how to post register plz write vba code
    no. 2
    i can data enter exel sheet 1 example january 30 day record
    i can data enter exel sheet 2 example Feb. 30 day record
    i can data enter exel sheet 3 example March 30 day record
    ok…

    import 3 Sheet All data import (Sheet 4)
    and every day entry sheet 1,2,3 update Sheet 4
    plz write vba code
    thanks best regards
    Nisar ali

  43. The next invoice number is going to save me so much time and I would love to implement this invoice register too bu is it possible to save this to a worksheet in another workbook?

  44. Hi bill, Thank you for this tips but I was hoping you in how to make another worksheet to be save on the same register list worksheet on the same work book. can you show me the VBA for that. please help me

  45. Hey Bill, thank u very much for the video explanation, I'm using ur Next Invoice, Save in new worksheet and Invoice Register, Is possible to make a Stock Register like the same as we can entry purchase units and Invoiced items will be deducted from the stock automatically??
    or
    I'm asking too much from the excel

    Thank for everything

  46. Hi Bill I have successfully been using your invoice register for the past few months and it has been a god send I have added more columns showing cost price, invoice value and profit margin. I have also added  filters to my lists so I can now search my register by name invoice or address. A big Thank you for your tutorial.
    As we are a carpet shop a lot of our clients pay a deposit on order and the balance is settled on completion of the work.I am trying to find a way that I can show the balance outstanding as paid. Currently I use a separate sheet for receipt of payment and have to go back into the register and enter the amount paid and date paid manually.
    Is there a macro code I can use to transfer the payment from the "receipt" sheet to the relevant invoice already on the register.
    I currently use "data validation" for the invoice number  and "VLOOKUP" which enters the clients details  and balance outstanding to the receipt from the  register. when I enter the amount paid the total box is altered simply by sum= .
    This is where I need the macro to transfer the amount paid to the register in a separate cell on the same line as the invoice details. I would then use the "save as pdf" macro for the receipt and "clear contents" macro to complete the task.
    Once again many thanks for your tutorials
    Howard

  47. First of all i have achievedalot watching your videos. Thanks a ton
    i am having problem when i am using this code to my current invoice excel sheet.
    I am getting Run-time error'9': subscript out of range
    can you help me out here ??

  48. HI, im not a expert of excel, so can i have the code please? and can i use both the codes together episode 1505 and 1808? actually i want auto numbering my invoices as well as want to save them on register sheet. thanks in advance.

  49. hi i am using this code.. and its awesome but i have one query about date column..i am using =now() fromula for date..and while saving in register the date column showing 42931.7458078704 this type of date..can u plz help me out of this

  50. Hi Bill, thank you so much for this it's awesome. I've had a got a most which I thought would be handy.

    I've had a go at adding FAQ#4 to FAQ#1 to insert a name after the invoice number
    INV-000-NAME
    Invoice # cell B9
    Name cell B11
    However, I get a compile error: Wrong number of arguments or invalid property assignment. What can you suggest?

  51. Dear sir ,

    Thanks so much for your teaching video. I have a one problem,if in register one data came zero value. All line go worng. Please help me to fix that error.

    Samtime data with no value

  52. Hi Bill

    I Am getting Run-time Error '1004'. Seems like the issue is with the line "NextRow = WS2.Cells(Rows.Count, 1).End(x1Up).Row + 1". Some of these fields are dropdown boxes – can that maybe be the problem? How do I fix that?

  53. Hi Bill,
    Hi,
    I have created a stock inventory sheet having ten products in MrExcel podcast 1505 auto next invoice.I want to reduce stock automatically when generate invoice. Just like auto postregister podcast 1808.
    Thanks in advance

  54. hey bill i made an invoice but i need to make discount rate calculate automatically when i need to calculacte it do u have any options 😀

  55. Sir, I want to transfer all details ( item, qnty, unit, price, ect ) to my invoice register. But unable to do that.. please help me

  56. Hi Bill,This helped me a tonne! I'd like to post the register to a different workbook rather than have the register in my existing one. For my purposes, it's an ECN LOG, instead of an INOVOICE REGISTER. Naturally, I'd like to just add to the register. Is there a way that we can add to the register and save it? Here's my code and it's working fine to save within the existing workbook: Sub PostToECNLOG()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("ECN")
    Set WS2 = Worksheets("ECN LOG")' Figure out which row is the next row
    NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1' Write the important values to ECN LOG
    WS2.Cells(NextRow, 1).Resize(1, 9).Value = Array(WS1.Range("C3"), WS1.Range("C4"), WS1.Range("C5"), WS1.Range("C6"), WS1.Range("C7"), WS1.Range("C8"), WS1.Range("C9"), WS1.Range("C10"), WS1.Range("C11"))End Sub

  57. Hey Bill, I had made a few years ago an Excel invoice using your videos. One of the codes for the file path I had put in activeworkbook.saveas newfn, fileformat:=xlopenxmlworkbook for my PC. Now I need to alter it for a MacBook. How do I go about that? Or is there another reason why the code is bugging at this point.

  58. Hi Bill, thanks for the awesome video. its helps me in my business. i want to print out the invoice every time i make it. Can you also add a code from which i can printout every bill just by running the macro. hope you can help me.

  59. Dear Bill,

    Thank you for these valuable teachings, i request you to add one more video that shows how to restrict the macro command to be used if some cells of invoice are empty such as "description", "total amount" and "for". Because some times we would accidentally click save invoice button without adding any data to it. Please show us a code the saves and register invoice record only if it is filled any.

  60. Hi Bill Your Video has helped me a lot. Thank You very much for it. I would further like to know if we can update specific customer data from register in different sheets

  61. my invoice register needs to tell me the supplier and it has multiple lines per invoice to show what items were bought that day and quantity. My problem is when i range the table that shows item codes description quantity etc, it pulls across blank lines not just rows with items on them. i also need to be able to paste the suppliers name and invoice number per row per item. that is causing issues too. Any solutions? eg: Inv#1 Supplier 1. buys 4 products i need to show 4 rows with suppliers name and each items per row. Inv#2 Supplier2. Buys 1 Product. I need to show supplier name and only the one product they purchased. ?

  62. Hi,
    I was trying to copy the exact code but i'm getting error on the last statement.
    Can you please help?
    Thanks.

    Compile error:
    Expected: end of statement

    Sub PostRegister()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("Invoice")
    Set WS2 = Worksheets("Register")

    ' Figure out which row is the next row

    NextRow = WS2.Cells(Rows.Count, 1) .End (xlUp) .Row + 1

    End Sub

  63. Hi Bill, thank you for these helpful tips!
    But Instead of saving the register list in another worksheet in same file, i want to save it in another excel workbook, advise tips on the same, Thanks in advance… Cheers…!!

  64. is it possible to somehow link those sheets automatically to their specified invoice number in the registry????????????

    edit: I forgot to mention thanks for your knowledge has been an immense help to my business.. love from india….

  65. Is there a macro that allows me to save invoices to certain folders based on company name and customer id number????

  66. HI SIR.

    Your video is awesome. My problem was resolved.

    But what should be done if the bill product is more or less

    As I have made a file.

    Also, invoice can be saved in Excel

    How to send them the file for correction.

  67. Hi, your video is so helpful!! May I ask what if I have to register the product details as well? I have different items in each invoice and would like to copy to the register sheet to summarize the total items and quantity sold for the same date. Thank you so much again for the awesome videos, and look forward to your solutions.

  68. Very good job, I need a little help: After I save data to Register if I change data for the same invoice number is there possible to update the old ones and not to register again after I push Save Button? Because to the save location on pc it replaces with new one, but not in the Register.

    Thank you in advance!!
    Best Regards!!

  69. How do I restrict the entry to post to register and create a msg box appear invoice No already saved and generated if the bill No Matches the

  70. Thanks Sir, it helps me a lot but I tried to copied full Description details under 1 invoice. How to do? Plz guide

  71. Hi, thank you for the video. I have a special need where I want to insert a unique serial number for each product based on quantity. For instance, if the quantity of first product is 1, I want to display "R(today's date)A". If the quantity of next product is 2, I want to display "R(today's date)B-C"

    Is there any possibility?

  72. Sir, I've created an invoice whose data is clicked on Save Button via VBA COOING, and the transfer of invoice data is transferred to another. But the formula (total / sum) of the cell (total / sum) formula did not contain the formula (total / sum) is not active, after the data transfer in the subsection, the cell (total / sum) form has to be rewritten again. We will benefit if you offer a video of the solution. If you gave your mail address, I could share my file if you want me to understand my problem.

    E-Mail: [email protected]@t. (Rony ==== 01710 740473.)

  73. Thank you for this video. I would like to invite you to visit our website where you can create a free account and start invoicing using ready-to-use invoice templates.
    https://aliphia.com/en/

  74. Hi Bill, thank you for these podcasts – I've managed to create the next invoice number and the register. I now need to be able to save a copy of the invoice itself and struggling with this. It would be really handy to be able to log the content list of the invoice as well as I'm recording ticket/docket numbers and be able to hyperlink to the invoice.

Leave a Reply

Your email address will not be published. Required fields are marked *