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!
@cdmassey987
9 жыл бұрын
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
@joem112
11 жыл бұрын
Bill you are fantastic. I loved the way you used Resize to enter the four values via an array rather than four separate lines of code.
@Dupton-vh1lk
11 жыл бұрын
This worked out FANTASTIC! Very excited to show my boss this added little gem to an already great spreadsheet. Thank you again!
@santoshrnojha
9 жыл бұрын
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 :)
@ishmam00011
Жыл бұрын
Hey guys. I am using this method for my business and it is GREAT. Thank you mr excel for this great video. I have a question. Im using an invoice where i have added a vlooup to gather customer and product data. Now im wondering if i can track inventory too. Any bright ideas? Thanks in advance
@anthonyhoang1446
8 жыл бұрын
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
@papaghys
3 жыл бұрын
Great podcasts 1505 and 1508! Can't stop looking at them and the hints from the website. Thanks. I was asking myself the same question and i can't find the answer... did you manage to get this working?
@DivaldoCarlos06
8 жыл бұрын
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.
@djwood8440
9 жыл бұрын
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.
@melanieolivant1386
5 жыл бұрын
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.
@RDSTeacher
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
@SathishVify
5 жыл бұрын
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?
@SandraSaysStuff
2 жыл бұрын
What if I have multiple invoices as separate, additional worksheets? How do I get Excel to pull this same information from each one of the worksheets in the workbook (except for the Register worksheet, of course) and add it onto the register?
@thorbansellers4433
8 жыл бұрын
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.
@perfect7pools116
7 жыл бұрын
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?
@TheMcjamieG
8 жыл бұрын
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?
@sebastiancornejo6015
8 жыл бұрын
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!
@akshayagujrati8128
7 жыл бұрын
Thanks Bill this video and Chap 1505 Helped me very much.
@jerrygeorgenedumannil4272
7 жыл бұрын
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.
@janetchastain9309
10 жыл бұрын
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.
@VishalGupta-sj9to
6 жыл бұрын
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
@lynnebarrass6571
9 жыл бұрын
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.
@lyndidegenaar7149
7 жыл бұрын
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?
@siyonkarmona6929
10 жыл бұрын
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
@malcolmmcmaster651
3 жыл бұрын
Amazing stuff! is the code for this podcast available ?
@saltshelpinghoundsrescue1758
5 жыл бұрын
This is the first time I've ever tried anything like this with Excel and it works! Your instructions are really easy to follow (makes me feel like I actually know what I'm doing - I don't!!). Can anyone tell me how I can automatically save this as a PDF please?
@ashishdhir3432
4 жыл бұрын
hello Bill, great job, this was of great help, can we use this detail to generate new invoice number and prohibit the user to manually enter the invoice number even by mistake
@Bostanle
9 жыл бұрын
Both podcasts 1505 and 1808 work great!! When can we see product register? Cheers Bo
@astritkurtishaj1023
5 жыл бұрын
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!!
@raghavanrsri
5 жыл бұрын
Is there any command to print first and save the file next
@sowinnie9066
6 жыл бұрын
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.
@cdmassey987
9 жыл бұрын
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?
@musebshaikh7617
3 жыл бұрын
Invoice No=01 Now suppose I am working for January. then I need my invoice Number to increase by 1 after that if the month changes to February then again my invoice Number should start from 1 and get increase by 1 for each new invoice. It should follow the same every time the month get's changed. what is the possible vba code for this. please reply
@jedpatten5055
9 жыл бұрын
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.
@Bostanle
9 жыл бұрын
***** Hey Bill, this is exactly what I need badly :) Waiting for your post!! Cheers Bo
@venkatanilgopa
7 жыл бұрын
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
@venkatanilgopa
7 жыл бұрын
Thanks for the reply
@unklabkids6862
7 жыл бұрын
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.
@unklabkids6862
7 жыл бұрын
Thank you for your quick reply, i'll try it.
@jpkeruakstaff4535
9 жыл бұрын
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
@safasecuresystems8685
10 жыл бұрын
Hi bill, Thank you so much it worked great. second time I opened and I run the program but invoice number not changed. and also file not saved in the location I given. How can I save the invoice in invoice number or name. how can I save the file in pdf format
@hotchandkarmani1801
9 жыл бұрын
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??
@guddukhaleel
7 жыл бұрын
hi bill this is my code i need to loop a values of B12 to E22 columns and rows please suggest me Sub posttoregister() Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Worksheets("invoice") Set ws2 = Worksheets("data") Dim count As Long nextrow = ws2.Cells(Rows.count, 2).End(xlUp).Row + 1 ws2.Cells(nextrow, 1).Resize(1, 10).Value = Array(ws1.Range("a10"), ws1.Range("f9"), ws1.Range("C4"), _ ws1.Range("b12"), ws1.Range("c12"), ws1.Range("d12"), ws1.Range("e12"), _ ws1.Range("f23"), ws1.Range("c34"), ws1.Range("c35")) End Sub Sub NextInvoice() Range("f9").Value = Range("f9").Value + 1 Range("c4:c9,A12:B22,D12:E22,f24,c34").ClearContents End Sub Sub SaveInvWithNewName() Dim NewFN As String posttoregister ' Copy Invoice to a new workbook ActiveSheet.Copy NewFN = "D:\BASF\Invoice\" & Range("f9").Value & ".xlsx" ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ' Copy Invoice to a new workbook PDF ActiveWorkbook.ExportAsFixedFormat xlTypePDF, Filename:="D:\BASF\PDF\" _ & Range("f9").Value & ".pdf", _ Quality:=xlQualityStandard, OpenAfterPublish:=True ActiveWorkbook.Close NextInvoice End Sub
@guddukhaleel
7 жыл бұрын
hi bill i need also a10,f9,c4,b12:e12,f23,c34,c35 then return b13:e22 if its true then copy the value of ws1 and paste it on ws2 range ("b13:e22") if its false escape it please help me
@tonig5601
7 жыл бұрын
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?
@6998256
6 жыл бұрын
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.
@6998256
6 жыл бұрын
thankyou bill for being an amazing teacher. It’s working. Looking forward to buy your power pivot and power BI soon.
@ka-sr1bz
9 жыл бұрын
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 ?
@wireharness4875
6 жыл бұрын
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
@NorthernStarMngmt
10 жыл бұрын
Great Video! works great. Just confused about 1 thing. What exact piece of code says to start at the first row under the headings, date, invoice #, customer etc.... ? Is that something to do with end.( xlup )? Works great, but i'm just not sure why. Thank you!
@NorthernStarMngmt
10 жыл бұрын
***** That all makes sense now, Thank you. Is there a way to specify where to start to looking up for the next blank row. Lets say I want my register to end at Row 10 because at row 11 I have text in the calls right across A11 B11 B12 B13... etc... So instead of looking from the very bottom for the next blank row, start looking at A10 and up, for the next blank row ...? Thank you so much
@NorthernStarMngmt
10 жыл бұрын
***** Sorry, i think i may have just clued in. If i replace row.count with the row number, comma, the column number, that's where it should start.
@craigbremner8520
3 жыл бұрын
Hi Mr Excel Your podcast are fantastic I have learnt so much. Just finished watching 1505 and 1808 and the work sheet is working a treat. As I am new to VBA do you have a podcast for the other coding SaveWithNewName that would be very helpful. Keep up the great work
@MrXL
3 жыл бұрын
There are several variations on the macro at www.mrexcel.com/news/next-invoice-number/
@ishmam00011
2 жыл бұрын
i have 2 questions 1. what if the WS2 is another workbook? 2. can i tell the macro a cell of my choice to begin with while entering information. and once again bill this is real education ! thank you in advance
@TheMcjamieG
8 жыл бұрын
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
@TheMcjamieG
8 жыл бұрын
Is it to much to ask if I send u some screen shots, u could maybe help me organise my jobs in a similar way? I'm going to look into buying your book I think
@TheMcjamieG
8 жыл бұрын
+Bill Jelen I'm very new to excell, I'm enjoying learning it but every time I find a good system I find better ways which is annoying lol I find myself starting again, problem I have is I have a lot of info which I find I'm writing over and over to place in different folders either for specific jobs, or a job register, wages... I need something that after I do the time sheet it auto fills all the other stuff in the other work books
@TheMcjamieG
8 жыл бұрын
+Bill Jelen I'm very new to excell, I'm enjoying learning it but every time I find a good system I find better ways which is annoying lol I find myself starting again, problem I have is I have a lot of info which I find I'm writing over and over to place in different folders either for specific jobs, or a job register, wages... I need something that after I do the time sheet it auto fills all the other stuff in the other work books
@TheMcjamieG
8 жыл бұрын
+Bill Jelen I'm very new to excell, I'm enjoying learning it but every time I find a good system I find better ways which is annoying lol I find myself starting again, problem I have is I have a lot of info which I find I'm writing over and over to place in different folders either for specific jobs, or a job register, wages... I need something that after I do the time sheet it auto fills all the other stuff in the other work books
@TheMcjamieG
8 жыл бұрын
+Bill Jelen yes please
@AnasSial
6 жыл бұрын
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.
@AnasSial
6 жыл бұрын
Bill Jelen thank you! Will give it a try and keep you updated!
@RyanDonnard
11 жыл бұрын
Bill, This is GREAT! Is it possible to enter in each item sold into separate lines in the register? You mentioned something like that near the end with "power pivots or a lot of VLOOKUPS" do you address how this can be done in another video?
@funnybomb2571
6 жыл бұрын
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….
@MrXL
6 жыл бұрын
Yes - this video has some info on creating hyperlinks via VBA: kzitem.info/news/bejne/u4KFuqiGaXyFgmU
@mobarakhossain2287
4 жыл бұрын
your tutorial has no comparison , really unique , could you make a formula for spellnumber?
@malavbhow
6 жыл бұрын
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...!!
@MrXL
6 жыл бұрын
Sub MoveToOther() Dim WBThis As Workbook Dim WBAllInvoices As Workbook Dim WSInvoice As Worksheet Dim SheetCount As Integer Set WBThis = ThisWorkbook Set WSInvoice = ActiveSheet Set WBAllInvoices = Workbooks.Open("C:\aaa\InvoiceCollection.xlsx") SheetCount = WBAllInvoices.Worksheets.Count WSInvoice.Copy after:=WBAllInvoices.Worksheets(SheetCount) WBAllInvoices.Save WBAllInvoices.Close MsgBox "Invoice saved to InvoiceCollection.xlsx" End Sub
@kaix92
10 жыл бұрын
I want to ask what do i add to this code to make the data i draw from the first worksheet to the 2nd retains the values and source formatting of the cell with it.
@anoopdeb
5 жыл бұрын
Thanks Sir, it helps me a lot but I tried to copied full Description details under 1 invoice. How to do? Plz guide
@salifdiallo9945
9 жыл бұрын
Merci Bill J'ai vu ce post au meilleur moment pour mon travail
@josephlewis8384
7 жыл бұрын
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?
@josephlewis8384
7 жыл бұрын
Thanks Bill for your Prompt response. The sheets work alright when editing in them manually but somehow the userform I created to do the same, seems to put blanks in the fields instead of filling it with data entered in the form. My workbook has five sheets. Three have the same formulas and only one of these sheets seems to do fine with userform entry, the other two having the same formulas almost identical structure have the formulas deleted instead of data entry which shows up as blank. I have to get back to these sheets and manually dragdown the formula from the cell above. So I have only managed to create a uselessform. With your userform solution, is there a way to have your code centralized and each page of the userform going through it before entering any data? Is it better to use formulas from within the userform module than to have them exposed in the spreadsheet?
@josephlewis8384
7 жыл бұрын
Hi Bill. Do you mean to post the code in this comment reply box? This is my email address: ratan_lewis@yahoo.co.in Each of the pages in my userform have a number of command buttons and text boxes There are 6 sheets in total in my workbook and 6 pages in my userform representing each sheet using the multipage control, all for the convenience of single point entry. I could send you the work book in excell format in its entirety and only if that's okay with you.
@hugoderosnay371
10 жыл бұрын
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!
@noorwazir
6 жыл бұрын
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.
@MrXL
6 жыл бұрын
You have made a great point. This is why you should use Quickbooks for invoicing instead of Excel. There is no good way to handle the real-life edge cases like the ones where you have to adjust an old invoice.
@chandaatanu
3 жыл бұрын
Excellent 👌 but just an add-on to this would be to check for duplicate invoice post to register and pop up a message box that the invoice number already exists on the register wksht.
@JeffSmith-xc4pc
9 жыл бұрын
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)
@JeffSmith-xc4pc
9 жыл бұрын
Jeff Smith I'm using Excel 2010
@louwrensduplessis2366
4 жыл бұрын
Good morning I have a problem with Excel help. When I click the ? I get the following error - The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. Does anybody know how to fix?
@wandamcclain4008
3 жыл бұрын
Hello Bill. Great Video. I am on a macbook and I am having some problems with errors. Currently I am getting a runtime error 9. I wrote the code like you did. I am getting the error where it says SetWS1 This is how it looks in excel Sub PostToRegister() Dim WS1 As Worksheet Dim WS2 As Worksheet Set WS1 = Worksheets(“Invoice”) Set WS2 = Worksheets(“Register”) NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1 WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range(“A5”), _ WS1.Range(“F10”), WS1.Range(“F9”), WS1.Range(“D11”), WS1.Range(“F33”)) End Sub. Is there a fee for assistance? Please help!!!
@MrXL
3 жыл бұрын
Runtime error 9 happens when you refer to a sheet that does not exist. You need to make sure that you have worksheets that are called Invoice and Register. If your sheets are called (for example) Inv or Invoices, you would have to change the code so it refers to “Inv” or “Invoices”.
@wandamcclain4008
3 жыл бұрын
Thank you. Yes I saw that on google but the Invoice is labeled invoice. Im so confused now
@MrXL
3 жыл бұрын
@@wandamcclain4008 This is a long shot... When I copy your code to my Excel's VBA window, the quotation marks around the word "Invoice" are slanted (also known as typographer's quotes). In VBA, the quotation marks need to be straight quotation marks. I tried running both of these lines of code: Set WS1 = Worksheets("Invoice") Set WS1 = Worksheets(“Invoice”) The first line (with straight quotes) worked. The second line (with slanted quotation marks) gave a runtime error 9. So... in your VBA, go to anywhere that you have a quotation mark. Delete it. Type a new quotation mark.
@wandamcclain4008
3 жыл бұрын
This has been awesome. I just need to get over that last error
@wandamcclain4008
3 жыл бұрын
BILL YOU ARE THE BOMB! The quotation marks were the issue. Then i was getting the same kinda error different line. I just had to keep on typing it and running it and finally no error! I am forever grateful. I have learned so much!
@njcranes
4 жыл бұрын
I don't need this in particular but this is outstanding. I might create something just to use it!
@alik.f4331
10 жыл бұрын
Hi Bill, First of all thank you so much for all the effort you had for these On line video classes. Can you please tell me how to save the New Invoice in New folder with customer name instead of invoice No. Thank you again, God bless you
@AnasSial
7 жыл бұрын
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.
@AnasSial
7 жыл бұрын
Bill Jelen awesome! so I'll try the Application.DoEvents but to clarify, I'll be putting that after each line of the code?
@AnasSial
7 жыл бұрын
Bill Jelen so my VBA isn't showing app. doevents....I put it in anyway and it kept debugging. so I had to manually go about deleting each line every time it stopped to debug. it worked then pressing f8 each time. however it doesn't work unless I keep taking each line out. now when I don't have it inputted after each code line, it crashes.
@claudeperreault4924
9 жыл бұрын
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
@alrayaalkuwaitia5936
8 жыл бұрын
hi bill can you please help to hyperlink invoice number in register to open saved file with the same invoice number
@ozidocs
3 жыл бұрын
hi in mac how do u know the folder example NewFn = "c:\aaa\inv how do i enter the same in mac what folder format should i enter
@MrXL
3 жыл бұрын
Here is one way to figure it out. 1. Create a new workbook and save it in the desired folder. 2. From that workbook, open the VBA editor. 3. In VBA, use the menu and choose View, Immediate Window. The immediate window opens and a flashing cursor will be there. 4. Type the following line and press enter: Debug.Print ActiveWorkbook.Path 5. Type the following line and press enter: Debug.Print ActiveWorkbook.FullName You should be able to see from the results of those two queries the format for the path and the path separator.
@jkstone459
10 жыл бұрын
Hi Bill! First off, THANK YOU! Your classes have helped me simplify SEVERAL functions in my office! Secondly, I was able to get named cells to work as I needed them to, but I am struggling with my range including multiple cells. For example, I want the cell in my register that contains the invoice number (requisition number in my case) to reflect the cell on my original worksheet that contains the invoice number PLUS the cell adjacent to it that contains text. Is there a way to do this? I tried separating the cells with a comma within the range, and I tried naming the two adjacent cells together and then referencing that named range within the array range. I know it is one of those palm slap things, but what am I doing wrong? This current code produces a blank cell on my register, when I KNOW there is values being generated because I can see them within the actual worksheet. THANKS in advance for your help!! WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(Range("ReqDate"), WS1.Range("H7:I7"), Range("VendorName"), Range("ReqTot"))
@jkstone459
10 жыл бұрын
***** THANK YOU!!! That worked PERFECTLY!!!!
@abdidekmuhumed4510
5 жыл бұрын
This ver useful and realy it is what we have been searching although you haried up. Thank you Mr excel.
@jatingera357
7 жыл бұрын
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 ??
@karrendapar9516
6 жыл бұрын
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
@MrXL
6 жыл бұрын
Hello Karren - You were really close. Excel does not want a space before the periods. Use this: NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
@karrendapar9516
6 жыл бұрын
Hi, Thanks for the reply. Yeah, we figured it out awhile ago and it's now working! awesome video! Thanks :) May I know if you have a code as well to save the sheet as pdf?
@MrXL
6 жыл бұрын
For Save as PDF code, see: www.mrexcel.com/news/next-invoice-number/
@karrendapar9516
6 жыл бұрын
Thank you! Unfortunately, I'm using MAC laptop. Appreciate if you also have a code for MAC. :)
@MrXL
6 жыл бұрын
I have no experience with a Mac. Try googling for Mac Excel VBA Save as PDF. Someone must have it.
@xTechnologicalx
8 жыл бұрын
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
@BananaFromMars
6 жыл бұрын
Did you figure this out? I have the same problem and as a Excel noobie, can't find the answer using Google..
@carolinejccarolinejc707
6 жыл бұрын
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. ?
@MrXL
6 жыл бұрын
Is there a field that will tell you if something was purchased? For example, lets say your quantity fields were in E11 through E19. I would loop through each row from 10 to 19 and see if column E was > 0. If it is, then copy the data over. For i = 10 to 19 If Worksheets("Invoice").Cells(i, 5).Value > 0 then ' Code here to write this line to the register End If Next i
@scottylabeau4921
6 жыл бұрын
Is there a macro that allows me to save invoices to certain folders based on company name and customer id number????
@MrXL
6 жыл бұрын
You could modify the macro in the Podcast 1505 video to do this. Right now, it weaves the Invoice Number in the file name: NewFN = "C:\FolderNameHere\Inv" & Range("E5").Value & ".xlsx" If you want to have the folder name be the customer account in C3 and the file name to be a combination of the Customer in D3 and the Invoice in E5: NewFN = "C:\" & Range("C3").Value & "\" & Range("D3") & Range("E5").Value & ".xlsx"
@mehulsavla3202
5 жыл бұрын
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
@niroshanmarasinhge4641
7 жыл бұрын
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
@niroshanmarasinhge4641
7 жыл бұрын
Bill Jelen sir I mean sheet 1 data. If it zero . example I copy discount . but always discount line not no be filled. So at that time what needs to do ?.
@petrsuchoj6888
10 жыл бұрын
Hi Bill, great video, as always. B tw, how can i create a register in a different xlsx file and different place? Thank you
@naumansultan8094
5 жыл бұрын
Hey works but it saves 2 times single entry what should i do
@nammarcr33737
4 жыл бұрын
How apply this opposite data table to generate invoice automatically
@jolandibornman8186
3 жыл бұрын
Hi Bill, great video and it works, however can you please assist me with a code to copy the info onto a seperate workbook, i used your code on podcast 1505 also and found that when i save and close, the data on the register is stored and updated to the saved xlxs document and not the master xlsm copy, thanks in advance, Jolandi
@MrXL
3 жыл бұрын
It sounds like you are close. Can you copy your existing code from VBA and paste it in a reply here? I can then copy it to my Excel and troubleshoot.
@jolandibornman8186
3 жыл бұрын
@@MrXL Thank you so much, its a combination of your code and tech excel's code, so basically we have a project cost sheet where it works on the same concept of an invoice where we can calculate the cost of the job and the profit we stand to make and the basic info like the jobcard number, rfq number, po number, total amount etc. needs to go to a register which also has blank columns between the info that needs to be recorded from the project cost sheet and the blank columns for other info to be recorded manually at a later stage when for when we get go ahead from the client to do the work. So by saying this, here is the code - thank you once again its much appreciated I don't mind the following two options 1. the master project cost sheet to be saved per the jobcard number as a single sheet in a xlsx workbook and the register to be updated on the master xlsm copy or 2. the register as a seperate xlsx workbook and the info to be copied and saved from the master xlsm workbook. Sub FilterQuantities() ' ' FilterQuantities Macro ActiveSheet.Range("$B$10:$R$823").AutoFilter Field:=5, Criteria1:="" End Sub Sub SaveNewProjectCostSheet() Dim path As String Dim filename1 As String path = "E:\Project Cost Sheets\" filename1 = Range("D2") Filename2 = Range("D7") Filename3 = Range("D8") Filename4 = Range("I4") Filename5 = Range("E823") Application.DisplayAlerts = False ActiveWorkbook.SaveAS Filename:=path & "PC" & filename1 & " - " & Filename2 & " - " & Filename3 & " - " & Filename4 & " - " & Filename5 & ".xlsx", FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = True MsgBox "Saved!" End Sub Sub NextProjectCostSheet() Range("D2").Value = Range("D2").Value + 1 MsgBox "New PC Number Created!" End Sub Sub PostToRegister() Dim WS1 As Worksheet Dim WS2 As Worksheet Set WS1 = Worksheets("Master") Set WS2 = Worksheets("PCS Register") ' which row is the next row NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1 ' Copy important info to the register WS2.Cells(NextRow, 1).Resize(1).Value = Array(WS1.Range("D2")) WS2.Cells(NextRow, 3).Resize(1).Value = Array(WS1.Range("I7")) WS2.Cells(NextRow, 4).Resize(1).Value = Array(WS1.Range("D7")) WS2.Cells(NextRow, 5).Resize(1).Value = Array(WS1.Range("D8")) WS2.Cells(NextRow, 15).Resize(1).Value = Array(WS1.Range("D4")) WS2.Cells(NextRow, 16).Resize(1).Value = Array(WS1.Range("D5")) WS2.Cells(NextRow, 18).Resize(1).Value = Array(WS1.Range("E823")) WS2.Cells(NextRow, 20).Resize(1).Value = Array(WS1.Range("Total")) WS2.Cells(NextRow, 21).Resize(1).Value = Array(WS1.Range("E822")) WS2.Cells(NextRow, 22).Resize(1).Value = Array(WS1.Range("I4")) WS2.Cells(NextRow, 24).Resize(1).Value = Array(WS1.Range("D6")) WS2.Cells(NextRow, 25).Resize(1).Value = Array(WS1.Range("E842")) MsgBox "Posted!" End Sub Sub ClearContents() Range("D4:E8").ClearContents Range("I4:L8").ClearContents Range("F11:F807").ClearContents Range("G812:G813").ClearContents Range("G826:L830").ClearContents Range("E842").ClearContents Range("E826:E829").ClearContents Range("E842").ClearContents ' Unfilter Macro ActiveSheet.Range("$B$10:$R$823").AutoFilter Field:=5 MsgBox "Cleared!" End Sub
@jessicamaxwell5694
10 жыл бұрын
How can you do this with multiple worksheets. I have multiple invoices in different worksheets that I would like to copy to one register. I have 10 different worksheet/invoices all the worksheets are in the same workbook. All worksheets use the same cell numbers for date, invoice, total amount, ect..
@bronteprojects4828
9 жыл бұрын
Jessica Maxwell Hi Jessica, did you get an answer on this? its exactly what i want to be able to do also
@BillJelenVideos
9 жыл бұрын
Bronte Projects It is possible to loop through multiple worksheets. Any good Excel consultant can quote a project and knock this code out in less than a day. As a good place to start, check with Oz at datascopic.net.
@bronteprojects4828
9 жыл бұрын
Never mind, I sussed it out myself. Sub PostToRegister() Dim WS2 As Worksheet Set WS2 = Worksheets("Register") ' Figure out which row is next NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1 ' Write the important values to register WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(ActiveSheet.Range("K5"), ActiveSheet.Range("K4"), _ ActiveSheet.Range("K8"), ActiveSheet.Range("A13")) End Sub
@jodymacisaac8829
2 жыл бұрын
I have it all written out the same as you and it keeps telling me error object required. What am I missing?
@jodymacisaac8829
2 жыл бұрын
Sub PostToRegister() Dim WS2 As Worksheet Dim WS4 As Worksheet Set WS2 = Worksheets("Invoice") Set WS4 = Worksheets("Register") ' Figure out which row is the next row NextRow = WS4.Cells(Row.Count, 1).End(xlUp).Row + 1 MsgBox NextRow ' Write the important values to Register WS4.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS2.Range("B3"), WS2.Range("K5"), _ WS2.Range("B23"), Range("InvTot")) End Sub
@MrXL
2 жыл бұрын
@@jodymacisaac8829 when you get the error, press Debug. The line in yellow is the problem. I suspect you don’t have a worksheet named Invoice or Register. Those names have to match exactly. If the code refers to Register and yours is Reg2022 or Registers, you get that error.
@thebesttechnical3608
5 жыл бұрын
Thanks for sharing a good and fast way.
@pacaviation7854
10 жыл бұрын
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.
@pacaviation7854
10 жыл бұрын
Sub PostToRegister() Dim WS1 As Worksheet Dim WS2 As Worksheet Set WS1 = Worksheets("PartsBase Quote") Set WS2 = Worksheets("Register") ' Figure Out Which row is the next row NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1 ' Write the important values to register WS2.Cells(NextRow, 1).Resize(1, 8).Value = Array(WS1.Range("G5"), WS1.Range("G6"), WS1.Range("G41"), WS1.Range("B8"), WS1.Range("B7"), WS1.Range("B11"), WS1.Range("B9"), WS1.Range("B10")) End Sub Sub NextInvoice() OldInvNumber = Range("G6").Value InvAlpha = Left(OldInvNumber, 2) InvNum = Mid(OldInvNumber, 3, 4) + 1 Range("G6").Value = InvAlpha & InvNum Range("A18:F38").ClearContents End Sub Sub SaveInvAsPDF() NewFN = "C:\Users\Pac\Desktop\PartsBase Quotes\" & Range("G6").Value & ".pdf" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True NextInvoice End Sub Sub SaveInvWithNewName() Dim NewFN As Variant PostToRegister ' Copy Invoice to a new Workbook ActiveSheet.Copy NewFN = "C:\Users\Pac\Desktop\PartsBase Quotes\" & Range("G6").Value & ".xlsx" ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close NextInvoice End Sub
@davindersingh-kq2wv
7 жыл бұрын
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.
@davindersingh-kq2wv
7 жыл бұрын
ok i will try to do this.. but i asked you another question . can i use them both..?thanks for your help sir.
@davindersingh-kq2wv
7 жыл бұрын
both the videos helped me a lot. thanks for the wonderful work. i have combine the code and done some changes as per my requirement. everything is working too good. formula i am working with is save pdf as well as excel file to a folder. but only thing i am missing is to create a hyperlink of new saved file in register worksheet. it will be more easy to open the file without going any where. can you help me in this...? as i said before im not expert so need your guidance. thanks in advance.
@davindersingh-kq2wv
7 жыл бұрын
bill i come up with an another problem, hope to see a reply. everything was working so fine and then i tried to clear content of merged cell. i end up with a error " we cant do that to a merged cell" error 1004. can you help me? im very close to finish my project.
@jhoharahbuisan7819
7 жыл бұрын
good
@li3chogmail
7 жыл бұрын
This is so amazing!!! Thanks for sharing your expertise Bill :)
@jiriruzicka4924
7 жыл бұрын
Can i have little advice? How can i do exactly this but to another excel file?
@jiriruzicka4924
7 жыл бұрын
Bill Jelen thank you for quick reply .... will have look into it very soon
@jiriruzicka4924
7 жыл бұрын
Thank you so much. Works great
@bharatchandka9839
5 жыл бұрын
I am facing this error "Run-time error'1004' application-defined or object-defined error. Please help
@khrisduarte
10 жыл бұрын
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!!!
@acsamalabo9138
10 жыл бұрын
***** Sir, how to collect the items listed on the Invoice? I want to summarize all the items I made in the invoice. Is this possible?
@hatimali93
9 жыл бұрын
Why did the file Format change after saving using macro??
@hatimali93
9 жыл бұрын
Hatim Ali I mean Font Style and Colour
@farrhanwit7817
8 жыл бұрын
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?
@farrhanwit7817
8 жыл бұрын
Bill, thanks. I was able to add multiple columns to the generated register. However, I am just stuck at a place which is at the bottom of the invoice there is the total cell which has a formula entered in it. Now as the macro copies the line items along with the total which is also I want but not with the formula, as it returns #REF, rightly so. Is there any way around to paste only the value of that total in the register? Thanks :)
@farrhanwit7817
8 жыл бұрын
As needed. Thank you :)
@victorlobo6975
8 жыл бұрын
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?
@victorlobo6975
8 жыл бұрын
Thank you for informing me
@victorlobo6975
8 жыл бұрын
But if i use =Now() it does not stay online (up to date with the time
@laddjoel4
8 жыл бұрын
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:\Users\Joel\Desktop\LF Auto Works\Inv" & Range("H12").Value & ".xlsx" ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close NextInvoice End Sub
@laddjoel4
8 жыл бұрын
+Bill Jelen I have one more question. After I save and clear, if I open one of the saved invoices to make a change, how can I have it update the register as well when I resave it?
@nickleo108
8 жыл бұрын
sir, i have seen a msg on display (subscript out of range) runtime error 9 i have set 4 module
@nickleo108
8 жыл бұрын
thank you. i have found.
@MQasim-un1ne
6 жыл бұрын
Nikku Singh brother i'm facing same error how did you solve this error
@MQasim-un1ne
6 жыл бұрын
Bill Jelen Hope your'e doing well, i'm facing run subscript out of range while putting this code although i checked all spell
@jalia1998
11 жыл бұрын
hello bill, I've created a calender that has some list boxes and some text boxes in it, but when I change the month the list boxes shrink. could you please help? I'll be glad to send you the work book and you can share the code if you'd like.
@matres2011
11 жыл бұрын
Hy Bill. I have daone all exactly as you shoued me and at the end i screw something up. I can not seem to make all of the modules run at once. I have the modules with the inv nr and save as pdf and clear all working with the same button, but i can not manage to inplement thisone to... Pls HELP... It is the first time i try something like this.
@Ejaz.kahoot
7 жыл бұрын
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
@Ejaz.kahoot
7 жыл бұрын
Bill Jelen thanks for your prompt and detailed answer.
@RajeshSingh-ce1un
8 жыл бұрын
Hartley Thanking You Sir for 1505 & now I trying for 1808 so help me ok aging thanking You Very Much
Пікірлер: 231