MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to print one page multiple times .....

Posted by Edwin on March 14, 2001 3:36 AM

First of all my English isn't that good but I will try:

I have a one page sheet which I want to print several times. But here it comes: How can I increase the value of one cell eacht time a page is printed.

For example:
the first page has cell B3 the value: 2001001
on the second page the value of B3 should than be 2001002

Can anybody help me?

Posted by Dave Hawley on March 14, 2001 5:35 AM

Hi Edwin

You will need a macro for this. The one below includes all Print settings and prints the active sheet 10 times, increasing the value of cell B3 by 1 each time.

To use it Push Alt+F11 and go to insert>Module, paste in the code and change the print settings to suit(You may want to record a macro setting your print settings and use that).

Push Alt+Q and Save.
Push Alt+F8, select "PrintXtimes" and click Run.


OzGrid Business Applications

Posted by Squirrel on March 14, 2001 7:47 AM

Re: Did dave forget the code?.....

I can use this also Dave but Is there something missing?

Posted by Dave Hawley on March 14, 2001 7:54 AM

It would seem so :)

Sub PrintXTimes()
Dim i As Integer
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
For i = 1 To 10
Range("B3") = Range("B3") + 1
ActiveWindow.SelectedSheets.PrintOut _
Copies:=1, Preview:=True, Collate:=True
Next i
End Sub

OzGrid Business Applications