Consecutive print numbers

[Lisa]

How can I print consecutive numbers on a print of the same doc...
Say I want to print 50 copies of the same doc and I want it to show on the
print out 1 of 50, 2 of 50, 3 of 50 etc etc...
Can this be done as a footer???

 

[Jacob Skaria]

Dear Lisa

Use the below macro. If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11.
Insert a module and paste the below code. Save. Get back to Workbook.
Tools|Macro|Run macro

Sub PrintMultiplewithFooterChange()

For intTemp = 1 To 50
ActiveSheet.PageSetup.CenterFooter = "Page " & intTemp & " of 50"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next intTemp

End Sub

If this post helps click Yes

 

[Jacob Skaria]

Revised to avoid the screen updates....

Sub PrintMultiplewithFooterChange()

Application.ScreenUpdating = False
For intTemp = 1 To 50
ActiveSheet.PageSetup.CenterFooter = "Page " & intTemp & " of 50"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next intTemp
Application.ScreenUpdating = True

End Sub

If this post helps click Yes

 

[Gord Dibben]

From Ron de Bruin.......................

Sub PrintCopies_ActiveSheet_1()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many copies do you want", _
Type:=1)

For CopieNumber = 1 To CopiesCount
With ActiveSheet
' This example print the number in cell A1
.Range("A1").Value = CopieNumber & " of " & CopiesCount

'If you want the number in the footer use this line
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut
End With
Next CopieNumber
End Sub


Gord Dibben MS Excel MVP