auto sequential number generator

[rvanbrussel]

Hi:

Not very good a VBA and found from Mr. Excel routine that will automatically generate a sequential number for each new sales order:

Sub SeqNumGenerator()
Range("G4").Value = Range("G4").Value + 1
End Sub

We print out 20-25 sales orders at a time to fill in manually when a customer comes into our shop. What I need to do next and have not been able to figure out is how to send the print command after the next number has been generated. Any help is appreciated. We do the sequential number/print process manually now and it is labor intensive. Thank you in advance for any help and direction

 

[davidam]

I have a small business and I use VBA to generate a series of invoices:

Public Sub Print_One()
Call Populate_Invoice
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Main").Activate
ThisWorkbook.Worksheets("Main").Range("A1:AD62").Select
Selection.PrintOut Copies:=1
mergeSheet.Activate
Application.ScreenUpdating = True
End Sub

The sub 'Populate_Invoice' places the data onto a worksheet, which is then printed.
This sub is itself inside of a Do Loop
You could start with :

x = 0
Do
Call number generator sub
'and have it place the number on you worksheet
Call Print_One 'as above
x = x + 1
Loop While x < 25

 

[rvanbrussel]

Thanks to all for your replies. Maybe I was not so clear because I am really novice when talking about macros. The Range/Value of G4 is the actual single cell that contains the number on my spreadsheet which needs to advance one digit each time the print command executes until the routine is ended. So we use, as a specific example, 1405-xxx where 1405 is year 2014 month of May and the xxx is a sequential number from 001 - xxx depending on the number of sales orders we generate for any given month. We print out 25 or so sales orders at a time and before each print my admin manually changes for example 001 to 002 then hits print again. All cells on the sales order except for G4 are static and are reprinted with the information we need to account for a customers specific work request. This is what I would like to automate. We tried this with no success in QuickBooks so have reverted to Excel to handle this chore. BTW I use MSOffice 2011 for Mac (not sure if this changes anything). For me with my experience the simpler the better. So I found the macro noted in my first message but do not know how to include a macro that executes the print command once the new number in a series is generated in cell G4. And then I need to tell it when to stop executing....like when after 25 copies are printed. I hope this makes some sense and please ask questions.

 

[davidam]

Type first invoice number in range G4

Public Sub Print_Invoices()
Dim invoiceRange as Range
Set invoiceRange = Range("G4")
x = 0
Do
Range("put the range of your invoice that you want printed here").Select
Selection.PrintOut Copies:=1
invoiceRange.Value = invoiceRange.value + 1
x = x + 1
Loop While x < 25 'you can change this number to anything and that is how many it will print *
End Sub

* You could also have a place on your invoice sheet that is not printed where you could type the numberr of sheets that you want to print
Say it is cell A1 ; then change the Loop While x < 25 to Loop While x < Range("A1").Value
Then if you change A1 to 60, or whatever, that is how many it would print

 

[davidam]

Actually, you don't really need to select the print range to print it. This would be more efficient

Public Sub Print_Invoices() 
Dim invoiceRange as Range, printRange as Range 
Set invoiceRange = Range("G4")
Set printRange = Range("put the range of your invoice that you want printed here") 
x = 0 
Do 
printRange.PrintOut Copies:=1 
invoiceRange.Value = invoiceRange.Value + 1 
x = x + 1 
Loop While x < 25 'you can change this number to anything and that is how many it will print * 
End Sub

 

[rvanbrussel]

Sorry for being so brain dead on this issue but a couple of questions.....
1) Do I totally replace the macro I referred to in my first post with the one you have suggested above or does that go after the macro I put in my first post?
2) Is "Invoices" the filename of your workbook or is it the named worksheet in the workbook or neither and I use generic "Invoice" as nomenclature for my macro?
3) When you say "range of invoice" you mean like A1 (upper left corner) to whatever the bottom right corner cell is correct??

And I really like the print limit Loop option. That will work well.

And thank you so much for sticking with me on this

 

[davidam]

1)This replaces your original one by using the line:
invoiceRange.Value = invoiceRange.Value +1
invoiceRange has become a range Variable and it refers to cell G4
2) I have just given the macro the Name: Print_Invoices. You would put it into a module in VBA
I am assuming your workbook has just the one worksheet that you use, so we do not need to worry about worksheet names as long as the worksheet with work order is active when you run the macro. If there are numerous worksheets then we need to be more specific about identifying the range variable
3) yes, the printRange (range variable) refers to whatever cells contain the part of the sheet that you want to print. it is: Range("top left Cell:bottom right Cell")
Additionally:
What I would do if it was mine is I would have a row or two along the top (or side) that are not printed. In these rows I would place a button that would be linked to this macro (First write the macro then draw a button from "Form Controls", right click the button and assign the macro...which would be this one). Then you would just click this button and it would print...no need to go into the macros
I am also seeing again that you are using month and year to derive your work order numbers. I would also use this unprinted area to set some parameters for inserting the right data into the work order itself. We can easily set up some expressions that create the month/year/number of the work order. Is G4 a cell that is part of the work order that gets printed?

 

[rvanbrussel]

Thank you so much. Yes G4 is part of the print area. It is that unique number that identifies each work order as a unique job and is what my admin uses as the invoice number and for billing in QuickBooks. Mr. Excel did show how to assign a button (the example was to use the draw feature to draw a shape as the button) to execute the macro. I can use form control as well now that you have explained it. So then what expressions should we use to automate the year/month/number for the work order?? I will set up the macro now and do what you suggested for the empty rows above the actual work order.

Thank you

 

[rvanbrussel]

HI David:
So I set up the macro in an xlsm workbook (only one worksheet). Inserted two blank rows for commands. Set the print range. Value for number of copies. Changed the invoiceRange to G6 (because of the 2 inserted rows) All seemed to go well but the number did not advance by a factor of 1. Not sure what happened. Any thoughts?

 

[davidam]

What do you have in cell G6? I assumed that your actual invoice number has the YYMM- in one cell, and that G6 is the cell to the right of that. If you have a dash in G6, the program will not see it as a number. We can fix that.

Can you post your code. Just copy the code and paste it in the reply form (go to advanced to see what your reply will look like) Put the word: code inside square brackets [] above your code, and: /code inside [] below it

PS have you ever used the debugger? Select, in this case, invoiceRange.Value, Click Debug>Quickwatch [Add] A window will open at the bottom of the page. Put an apostrophe in front of the line 'printRange.Print so that you can test the code without it actually printing. Then put your cursor at the right of the top line that contains Sub. Now step through the code with F8 and you can see invoiceRange.value changing.