-
auto numbered Vouchers need tracking sheet
I have an excel file that when it prints the vouchers requested it will auto number them. I am trying to find a way that i can create a log of all the numbers that were generated and than have it print after the voucher print.
I am lost on how to get the numbers from the vouchers to the tracking sheet.
Any Ideas?
-
I've updated your workbook.
Changes:
When opened, the user form will use the last entry, which will be the number of the last printed ticket, from sheet two to populate the 'From' text box.
After printing, the number of the last printed ticket will be added to the bottom of the list. Currently it only adds the last number, not all numbers printed.
-
I forgot to mention. For your print routine, I changed:
[VBA] Do Until z = y[/VBA]
to
[VBA] Do Until z >= y[/VBA]
otherwise it'll be printing ticket forever. z will never equal y with the current structure of the code. Once it loops through, z = 11 while y = 10
-
If you want it to print each individual ticket number, use this for your Print routine:
[vba]Private Sub cmdPrint_Click()
Dim x As Long, y As Long, z As Long
x = Me.TextBox1.Value
y = Me.TextBox2.Value
z = x - 0
Unload Me ' if not Excel will lock in Print Preview
On Error GoTo whoops
Do Until z >= y
Range("c13").Value = z + 1
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c27").Value = z + 2
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c41").Value = z + 3
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c55").Value = z + 4
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c69").Value = z + 5
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f13").Value = z + 6
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f27").Value = z + 7
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f41").Value = z + 8
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f55").Value = z + 9
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f69").Value = z + 10
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
z = z + 10
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Loop
whoops:
UserForm1.Show
End Sub[/vba]
-
OK so i made the changes you guys suggested and everything seems to be working but.......
when i tell it to print 1 and 6 batches the very first voucher on the sheet says either TRUE or FALSE. when i look at it in the print preview mode i do not see that.
And
If i tell it to start printing at 11 the first number on the log is always 12..
-
Try the attached version.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules