Page Numbers

[Jobe Gates]

I added a Page Number to the bottom of one sheet. I want to print multiple
copies of the same sheet but I want the page number to increment.

 

[John Thow]

I added a Page Number to the bottom of one sheet. I want to print multiple
copies of the same sheet but I want the page number to increment.

Hmmm. Are you saying you want to print each page several times but with
incrementing page numbers each time you print the same page?

Please clarify.

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Jobe Gates]

Yes that is exactly what I'm trying to do.

 

[John Thow]

Yes that is exactly what I'm trying to do.



OK, what you're going to have to do is take control of the printing process.
You will have to tell the application how many times to print each page and
what the total number of pages is. I've included below a simple macro that
uses input boxes to establish these values and call a function - also below -
to print a single page a number of times, incrementing the page number each
time.

[You only need the parameters to PageSetup that apply to your sheet. The code
below includes all of them so that you can decide which ones you need. Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page (worksheet) and
what the total number of pages is. You will need a macro that activates your
worksheets one by one and calls the function to print each sheet the
appropriate number of times. You will need to keep a count of the total
number of pages already printed add 1 to this and pass it to the StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get very messy
because you'll then need to establish the number of printed pages represented
by a single sheet, print the first of the multiple pages only, reset the start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple print
tasks. If you are not using a dedicated printer - or if you issue other print
commands while it's running - your output may get split up by other tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer, PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Jobe Gates]

John,

Thanks for the Macro, however I'm unfamiliar with how to put it in? Can you
instruct me?

Thanks,

Jobe

Yes that is exactly what I'm trying to do.



OK, what you're going to have to do is take control of the printing process.
You will have to tell the application how many times to print each page and
what the total number of pages is. I've included below a simple macro that
uses input boxes to establish these values and call a function - also below -
to print a single page a number of times, incrementing the page number each
time.

[You only need the parameters to PageSetup that apply to your sheet. The code
below includes all of them so that you can decide which ones you need. Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page (worksheet) and
what the total number of pages is. You will need a macro that activates your
worksheets one by one and calls the function to print each sheet the
appropriate number of times. You will need to keep a count of the total
number of pages already printed add 1 to this and pass it to the StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get very messy
because you'll then need to establish the number of printed pages represented
by a single sheet, print the first of the multiple pages only, reset the start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple print
tasks. If you are not using a dedicated printer - or if you issue other print
commands while it's running - your output may get split up by other tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer, PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Dave Peterson]

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (DoRepeatPrint)
and then click run.

John,

Thanks for the Macro, however I'm unfamiliar with how to put it in? Can you
instruct me?

Thanks,

Jobe

Yes that is exactly what I'm trying to do.


I added a Page Number to the bottom of one sheet. I want to print
multiple
copies of the same sheet but I want the page number to increment.

Hmmm. Are you saying you want to print each page several times but with
incrementing page numbers each time you print the same page?

Please clarify.

OK, what you're going to have to do is take control of the printing process.
You will have to tell the application how many times to print each page and
what the total number of pages is. I've included below a simple macro that
uses input boxes to establish these values and call a function - also below -
to print a single page a number of times, incrementing the page number each
time.

[You only need the parameters to PageSetup that apply to your sheet. The code
below includes all of them so that you can decide which ones you need. Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page (worksheet) and
what the total number of pages is. You will need a macro that activates your
worksheets one by one and calls the function to print each sheet the
appropriate number of times. You will need to keep a count of the total
number of pages already printed add 1 to this and pass it to the StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get very messy
because you'll then need to establish the number of printed pages represented
by a single sheet, print the first of the multiple pages only, reset the start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple print
tasks. If you are not using a dedicated printer - or if you issue other print
commands while it's running - your output may get split up by other tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer, PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Jobe Gates]

Thanks that was most helpful.

When I go to run the Macro I'm getting an error

Run-time error '1004':
Unable to set the PrintQuality property of the PageSetup class

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (DoRepeatPrint)
and then click run.

John,

Thanks for the Macro, however I'm unfamiliar with how to put it in? Can you
instruct me?

Thanks,

Jobe

Yes that is exactly what I'm trying to do.

wrote:

I added a Page Number to the bottom of one sheet. I want to print
multiple
copies of the same sheet but I want the page number to increment.

Hmmm. Are you saying you want to print each page several times but with
incrementing page numbers each time you print the same page?

Please clarify.

OK, what you're going to have to do is take control of the printing process.
You will have to tell the application how many times to print each

page
and

what the total number of pages is. I've included below a simple macro that
uses input boxes to establish these values and call a function - also below -
to print a single page a number of times, incrementing the page number each
time.

[You only need the parameters to PageSetup that apply to your sheet.

The
code

below includes all of them so that you can decide which ones you need. Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page

(worksheet)
and

what the total number of pages is. You will need a macro that

activates
your

worksheets one by one and calls the function to print each sheet the
appropriate number of times. You will need to keep a count of the total
number of pages already printed add 1 to this and pass it to the StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get very messy
because you'll then need to establish the number of printed pages represented
by a single sheet, print the first of the multiple pages only, reset

the
start

page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple print
tasks. If you are not using a dedicated printer - or if you issue

other
print

commands while it's running - your output may get split up by other tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer, PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Dave Peterson]

My guess:

Some printers support more options than others.

You could record a test macro to see if your printer supports this:
..PrintQuality = -3

When I recorded one, my line looked like:
..PrintQuality = 300

(my printer supports 75, 150, and 300 dots per inch.)

I don't have a guess what -3 means.

(If you're happy with the default print quality (I am with my 300dpi), you could
just comment out that one line (or even delete it). In fact, if you're going to
use this on different printers, it's probably better to not use it.)

Thanks that was most helpful.

When I go to run the Macro I'm getting an error

Run-time error '1004':
Unable to set the PrintQuality property of the PageSetup class

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (DoRepeatPrint)
and then click run.

John,

Thanks for the Macro, however I'm unfamiliar with how to put it in? Can you
instruct me?

Thanks,

Jobe
Yes that is exactly what I'm trying to do.

wrote:

I added a Page Number to the bottom of one sheet. I want to print
multiple
copies of the same sheet but I want the page number to increment.

Hmmm. Are you saying you want to print each page several times but
with
incrementing page numbers each time you print the same page?

Please clarify.

OK, what you're going to have to do is take control of the printing
process.
You will have to tell the application how many times to print each page
and
what the total number of pages is. I've included below a simple macro
that
uses input boxes to establish these values and call a function - also
below -
to print a single page a number of times, incrementing the page number
each
time.

[You only need the parameters to PageSetup that apply to your sheet. The
code
below includes all of them so that you can decide which ones you need.
Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page (worksheet)
and
what the total number of pages is. You will need a macro that activates
your
worksheets one by one and calls the function to print each sheet the
appropriate number of times. You will need to keep a count of the total
number of pages already printed add 1 to this and pass it to the StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get very
messy
because you'll then need to establish the number of printed pages
represented
by a single sheet, print the first of the multiple pages only, reset the
start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple print
tasks. If you are not using a dedicated printer - or if you issue other
print
commands while it's running - your output may get split up by other tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer,
PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Jobe Gates]

Thanks that worked. It's almost working perfectly. When the form prints
the Macro has pushed a column onto a second sheet.

My guess:

Some printers support more options than others.

You could record a test macro to see if your printer supports this:
.PrintQuality = -3

When I recorded one, my line looked like:
.PrintQuality = 300

(my printer supports 75, 150, and 300 dots per inch.)

I don't have a guess what -3 means.

(If you're happy with the default print quality (I am with my 300dpi), you could
just comment out that one line (or even delete it). In fact, if you're going to
use this on different printers, it's probably better to not use it.)

Thanks that was most helpful.

When I go to run the Macro I'm getting an error

Run-time error '1004':
Unable to set the PrintQuality property of the PageSetup class

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (DoRepeatPrint)
and then click run.

Jobe Gates wrote:

John,

Thanks for the Macro, however I'm unfamiliar with how to put it in?

Can
you

instruct me?

Thanks,

Jobe

wrote:

Yes that is exactly what I'm trying to do.

wrote:

I added a Page Number to the bottom of one sheet. I want to print
multiple
copies of the same sheet but I want the page number to increment.

Hmmm. Are you saying you want to print each page several times but
with
incrementing page numbers each time you print the same page?

Please clarify.

OK, what you're going to have to do is take control of the printing
process.
You will have to tell the application how many times to print each page
and
what the total number of pages is. I've included below a simple macro
that
uses input boxes to establish these values and call a function - also
below -
to print a single page a number of times, incrementing the page number
each
time.

[You only need the parameters to PageSetup that apply to your

sheet.
The

code
below includes all of them so that you can decide which ones you need.
Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page (worksheet)
and
what the total number of pages is. You will need a macro that activates
your
worksheets one by one and calls the function to print each sheet the
appropriate number of times. You will need to keep a count of the total
number of pages already printed add 1 to this and pass it to the StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get very
messy
because you'll then need to establish the number of printed pages
represented
by a single sheet, print the first of the multiple pages only,

reset
the

start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple print
tasks. If you are not using a dedicated printer - or if you issue other
print
commands while it's running - your output may get split up by

other
tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer,
PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

Thanks that was most helpful.

When I go to run the Macro I'm getting an error

Run-time error '1004':
Unable to set the PrintQuality property of the PageSetup class

Jobe,

Like I said, you only need the PageSetUp parameters that apply to you.

As Dave P says, just leave the PrintQuality line out.

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Dave Peterson]

Record a macro when you set the page setup

file|page setup|Page tab
Fit to 1 page wide (or what you want) by (leave blank) tall

Replace that portion in the code.

Look for lines like these:

.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False

Thanks that worked. It's almost working perfectly. When the form prints
the Macro has pushed a column onto a second sheet.

My guess:

Some printers support more options than others.

You could record a test macro to see if your printer supports this:
.PrintQuality = -3

When I recorded one, my line looked like:
.PrintQuality = 300

(my printer supports 75, 150, and 300 dots per inch.)

I don't have a guess what -3 means.

(If you're happy with the default print quality (I am with my 300dpi), you could
just comment out that one line (or even delete it). In fact, if you're going to
use this on different printers, it's probably better to not use it.)

Thanks that was most helpful.

When I go to run the Macro I'm getting an error

Run-time error '1004':
Unable to set the PrintQuality property of the PageSetup class

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (DoRepeatPrint)
and then click run.

Jobe Gates wrote:

John,

Thanks for the Macro, however I'm unfamiliar with how to put it in? Can
you
instruct me?

Thanks,

Jobe

wrote:

Yes that is exactly what I'm trying to do.


On Wed, 10 Dec 2003 11:13:36 -0600, "Jobe Gates"
<[email protected]>
wrote:

I added a Page Number to the bottom of one sheet. I want to print
multiple
copies of the same sheet but I want the page number to increment.

Hmmm. Are you saying you want to print each page several times but
with
incrementing page numbers each time you print the same page?

Please clarify.

OK, what you're going to have to do is take control of the printing
process.
You will have to tell the application how many times to print each
page
and
what the total number of pages is. I've included below a simple macro
that
uses input boxes to establish these values and call a function - also
below -
to print a single page a number of times, incrementing the page number
each
time.

[You only need the parameters to PageSetup that apply to your sheet.
The
code
below includes all of them so that you can decide which ones you need.
Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page
(worksheet)
and
what the total number of pages is. You will need a macro that
activates
your
worksheets one by one and calls the function to print each sheet the
appropriate number of times. You will need to keep a count of the
total
number of pages already printed add 1 to this and pass it to the
StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get very
messy
because you'll then need to establish the number of printed pages
represented
by a single sheet, print the first of the multiple pages only, reset
the
start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple
print
tasks. If you are not using a dedicated printer - or if you issue
other
print
commands while it's running - your output may get split up by other
tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer,
PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Jobe Gates]

Thanks guys it's working perfectly. Please let me know if I can return the
favor. Let me know if you need any Network or Systems help. Please feel
free to e-mail.

Record a macro when you set the page setup

file|page setup|Page tab
Fit to 1 page wide (or what you want) by (leave blank) tall

Replace that portion in the code.

Look for lines like these:

.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False

Thanks that worked. It's almost working perfectly. When the form prints
the Macro has pushed a column onto a second sheet.

My guess:

Some printers support more options than others.

You could record a test macro to see if your printer supports this:
.PrintQuality = -3

When I recorded one, my line looked like:
.PrintQuality = 300

(my printer supports 75, 150, and 300 dots per inch.)

I don't have a guess what -3 means.

(If you're happy with the default print quality (I am with my 300dpi),

you
could

just comment out that one line (or even delete it). In fact, if

you're
going to

use this on different printers, it's probably better to not use it.)



Jobe Gates wrote:

Thanks that was most helpful.

When I go to run the Macro I'm getting an error

Run-time error '1004':
Unable to set the PrintQuality property of the PageSetup class

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (DoRepeatPrint)
and then click run.

Jobe Gates wrote:

John,

Thanks for the Macro, however I'm unfamiliar with how to put it

in?
Can

you
instruct me?

Thanks,

Jobe

wrote:

Yes that is exactly what I'm trying to do.


On Wed, 10 Dec 2003 11:13:36 -0600, "Jobe Gates"
<[email protected]>
wrote:

I added a Page Number to the bottom of one sheet. I want

to
print

multiple
copies of the same sheet but I want the page number to increment.

Hmmm. Are you saying you want to print each page several

times
but

with
incrementing page numbers each time you print the same page?

Please clarify.

OK, what you're going to have to do is take control of the printing
process.
You will have to tell the application how many times to print each
page
and
what the total number of pages is. I've included below a

simple
macro

that
uses input boxes to establish these values and call a

function -
also

below -
to print a single page a number of times, incrementing the

page
number

each
time.

[You only need the parameters to PageSetup that apply to your sheet.
The
code
below includes all of them so that you can decide which ones

you
need.

Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page
(worksheet)
and
what the total number of pages is. You will need a macro that
activates
your
worksheets one by one and calls the function to print each

sheet
the

appropriate number of times. You will need to keep a count of the
total
number of pages already printed add 1 to this and pass it to the
StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all

get
very

messy
because you'll then need to establish the number of printed pages
represented
by a single sheet, print the first of the multiple pages only, reset
the
start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple
print
tasks. If you are not using a dedicated printer - or if you issue
other
print
commands while it's running - your output may get split up by other
tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer,
PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Dave Peterson]

Thanks for the offer--but if you see someone struggling that you can help, think
of us <bg>.

I would bet that John feels the same way.

Thanks guys it's working perfectly. Please let me know if I can return the
favor. Let me know if you need any Network or Systems help. Please feel
free to e-mail.

Record a macro when you set the page setup

file|page setup|Page tab
Fit to 1 page wide (or what you want) by (leave blank) tall

Replace that portion in the code.

Look for lines like these:

.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False

Thanks that worked. It's almost working perfectly. When the form prints
the Macro has pushed a column onto a second sheet.

My guess:

Some printers support more options than others.

You could record a test macro to see if your printer supports this:
.PrintQuality = -3

When I recorded one, my line looked like:
.PrintQuality = 300

(my printer supports 75, 150, and 300 dots per inch.)

I don't have a guess what -3 means.

(If you're happy with the default print quality (I am with my 300dpi), you
could
just comment out that one line (or even delete it). In fact, if you're
going to
use this on different printers, it's probably better to not use it.)



Jobe Gates wrote:

Thanks that was most helpful.

When I go to run the Macro I'm getting an error

Run-time error '1004':
Unable to set the PrintQuality property of the PageSetup class

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (DoRepeatPrint)
and then click run.

Jobe Gates wrote:

John,

Thanks for the Macro, however I'm unfamiliar with how to put it in?
Can
you
instruct me?

Thanks,

Jobe
On Thu, 11 Dec 2003 09:31:14 -0600, "Jobe Gates"
<[email protected]>
wrote:

Yes that is exactly what I'm trying to do.


On Wed, 10 Dec 2003 11:13:36 -0600, "Jobe Gates"
<[email protected]>
wrote:

I added a Page Number to the bottom of one sheet. I want to
print
multiple
copies of the same sheet but I want the page number to
increment.

Hmmm. Are you saying you want to print each page several times
but
with
incrementing page numbers each time you print the same page?

Please clarify.

OK, what you're going to have to do is take control of the
printing
process.
You will have to tell the application how many times to print each
page
and
what the total number of pages is. I've included below a simple
macro
that
uses input boxes to establish these values and call a function -
also
below -
to print a single page a number of times, incrementing the page
number
each
time.

[You only need the parameters to PageSetup that apply to your
sheet.
The
code
below includes all of them so that you can decide which ones you
need.
Some
may require modification to suit your environment.]

I assume you know how many times you want to print each page
(worksheet)
and
what the total number of pages is. You will need a macro that
activates
your
worksheets one by one and calls the function to print each sheet
the
appropriate number of times. You will need to keep a count of the
total
number of pages already printed add 1 to this and pass it to the
StartPage
parameter of the function on each call. Similarly for EndPage.

If any of your sheets is longer than a single page, it'll all get
very
messy
because you'll then need to establish the number of printed pages
represented
by a single sheet, print the first of the multiple pages only,
reset
the
start
page and end pages & print the next page only, etc. etc.

Something else to be aware of is that this process issues multiple
print
tasks. If you are not using a dedicated printer - or if you issue
other
print
commands while it's running - your output may get split up by
other
tasks.

Good luck!

(Watch out for line breaks...)

Function RepeatPagePrint(StartPage As Integer, EndPage As Integer,
PageTotal
As Integer)
'
Dim Kount As Integer

For Kount = StartPage To EndPage
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page " & Kount & " of " & PageTotal
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

End Function

Sub DoRepeatPrint()
Dim StPage As Integer
Dim EnPage As Integer
Dim PgTotal As Integer

StPage = Val(InputBox("Start Page Number"))
EnPage = Val(InputBox("Endpage"))
PgTotal = Val(InputBox("Total Pages"))

Call RepeatPagePrint(StPage, EnPage, PgTotal)

End Sub






--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

Thanks for the offer--but if you see someone struggling that you can help, think
of us <bg>.

I would bet that John feels the same way.

[Chop]

Affirmative. ;-)
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Jobe Gates]

Thanks again to you both.

Thanks for the offer--but if you see someone struggling that you can help, think
of us <bg>.

I would bet that John feels the same way.

[Chop]

Affirmative. ;-)
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[av]

I added a Page Number to the bottom of one sheet. I want to print multiple
copies of the same sheet but I want the page number to increment.

As per my post just sent, Excel with increment the page numbers
automatically and will not confuse the number of the *page* with
the number of the *copy*

avril