numbers (like invoice numbers) in Excel worksheet

[Max Bialystock]

I want to print out 70 copies of an order form. Each form needs to be
numbered. The first form to be printed will numbered "1". The last form to
be printed will be numbered "70".

If, in future, I need to print more of these forms, then next one I print
out should be numbered "71".

How do I do this, please?

 

[Vasant Nanavati]

Here's a very basic solution:

http://groups.google.com/group/micr...355d522594e?lnk=st&q=&rnum=1#bd93a355d522594e

__________________________________________________________________________

 

[Gord Dibben]

See Ron de Bruin's Print Tips site...........

http://www.rondebruin.nl/print.htm#number

The example below continue printing where It left off, such as today you print
numbered pages 1-25 and the next time when you enter 10 in the input box it
print 26-35.


Sub PrintCopies_ActiveSheet_2()
' This example print the number in cell A1
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

With ActiveSheet
If Not IsNumeric(.Range("A1").Value) Then .Range("A1").Value = 0

For CopieNumber = 1 To CopiesCount
.Range("A1").Value = .Range("A1").Value + 1

'Print the sheet
.PrintOut

Next CopieNumber
End With
End Sub


Gord Dibben MS Excel MVP

 

[Gary Keramidas]

give this a try. change the rng, the worksheet name and the cell the number is
in. watch for word wrap when you paste the code
i also have to set to preview instead of print.


Option Explicit
Sub print_invoices()
Dim EndNum As Long, StartNum As Long
Dim ws As Worksheet
Dim i As Long
Dim rng As Range
Dim bOK As Boolean

Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A1:E40")

StartNum = Application.InputBox("Enter Starting Number:", Type:=1)
EndNum = Application.InputBox("Enter number of copies to print:", Type:=1)
If StartNum = 0 Then GoTo Xit
If EndNum = 0 Then GoTo Xit
bOK = Application.Dialogs(xlDialogPrinterSetup).Show 'choose the
printer
If bOK = False Then GoTo Xit
Application.ScreenUpdating = False

For i = StartNum To EndNum
ws.Range("A1").Value = i
With ws.PageSetup
.HeaderMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.4)
.LeftMargin = Application.InchesToPoints(0.4)
.TopMargin = Application.InchesToPoints(1.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintArea = rng.Address
.CenterHorizontally = True
.CenterVertically = False
.Zoom = 100

End With
With ws
.PrintPreview
' .PrintOut Copies:=1, Collate:=True
End With

Next
Xit:
Application.ScreenUpdating = True
End Sub

 

[Gary Keramidas]

this
EndNum = Application.InputBox("Enter number of copies to print:", Type:=1)

should have been this

EndNum = Application.InputBox("Enter Ending number:", Type:=1)