Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default How do I print a one page spreadsheet multiple time, each with its own page number?

Hello:

I have a one page spreadsheet, essentially a template. I would like to be able to print as many copies as needed, but each copy has to print a unique sequence value, which increments by one every time it is printed.

This variable may require some formatting, but it's somewhat like a page number. Printing multiple copies is just reprinting page one to Excel; each page is labeled 1. I'd rather not copy my print range x number of times into the workbook, but that's what I did, just to get started. I don't have any VB experience; I was just trying to use Excel's existing functionality.

Thank you,

Laura Sallwasser
  #2   Report Post  
Roy Wagner
 
Posts: n/a
Default

Laura,

Test this in a new workbook.

For testing I used cell F1 as the storage location of the sequence number.
You can put it anywhere you want, just change the cell reference in the code
from F1 to your range. You can format your sequence # cell white on white for
example so it cannot be seen. With a little more work, we could simply read
the existing sequence number listed on the sheet, break out the numeric
portion, increment it, put it back together and toss on the sheet, if you
really don't want a placeholder cell.

Where you have the Sequence ID printed on the bottom of your form, add this
formula with your own version of text.

="Sequence " & F1

This produces: "Sequence 1001" if cell F1 contains 1001. That's a simple
structure, but it can be fairly complex if you want to be more creative with
it.

Create a new macro called PrintSequence, give it a shortcut key "ctrl-p" and
replace the code generated with the code below. You can name the macro and
assign the short cut as you see fit.

Roy

Sub PrintSequence()
'
' PrintSequence Macro
' Macro recorded (altered) 8/16/2005 by Roy Wagner
'
' Keyboard Shortcut: Ctrl+p
'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies as Integer

SequenceNumber = ActiveSheet.Range("F1").Value
x = InputBox("How many pages need to be printed", "How Many Pages?", "1")
If Val(x) < 1 Then Exit Sub
For UniqueCopies = 1 To x
ActiveSheet.Range("F1").Value = SequenceNumber
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
Next
End Sub



"lhsallwasser" wrote:


Hello:

I have a one page spreadsheet, essentially a template. I would like to
be able to print as many copies as needed, but each copy has to print a
unique sequence value, which increments by one every time it is
printed.

This variable may require some formatting, but it's somewhat like a
page number. Printing multiple copies is just reprinting page one to
Excel; each page is labeled 1. I'd rather not copy my print range x
number of times into the workbook, but that's what I did, just to get
started. I don't have any VB experience; I was just trying to use
Excel's existing functionality.

Thank you,

Laura Sallwasser


--
lhsallwasser

  #3   Report Post  
Roy Wagner
 
Posts: n/a
Default

Laura,

If you want to use the center footer, replace the FOR/NEXT loop with the one
below. You still need a placeholder on the worksheet, but the sequence number
statement is printed in the footer rather than on the sheet (only visible in
preview etc.). You can eliminate the formula on the sheet that put the text
and number together. I also changed it so it increments the number before
printing instead of after. If you ran it twice the other way, you would see
that the last copy from 1st run would have the same number as the first copy
of the 2nd run.

Roy

For UniqueCopies = 1 To x
SequenceNumber = SequenceNumber + 1
ActiveSheet.Range("F1").Value = SequenceNumber
ActiveSheet.PageSetup.CenterFooter = "Sequence" & Str(SequenceNumber)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next



"Roy Wagner" wrote:

Laura,

Test this in a new workbook.

For testing I used cell F1 as the storage location of the sequence number.
You can put it anywhere you want, just change the cell reference in the code
from F1 to your range. You can format your sequence # cell white on white for
example so it cannot be seen. With a little more work, we could simply read
the existing sequence number listed on the sheet, break out the numeric
portion, increment it, put it back together and toss on the sheet, if you
really don't want a placeholder cell.

Where you have the Sequence ID printed on the bottom of your form, add this
formula with your own version of text.

="Sequence " & F1

This produces: "Sequence 1001" if cell F1 contains 1001. That's a simple
structure, but it can be fairly complex if you want to be more creative with
it.

Create a new macro called PrintSequence, give it a shortcut key "ctrl-p" and
replace the code generated with the code below. You can name the macro and
assign the short cut as you see fit.

Roy

Sub PrintSequence()
'
' PrintSequence Macro
' Macro recorded (altered) 8/16/2005 by Roy Wagner
'
' Keyboard Shortcut: Ctrl+p
'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies as Integer

SequenceNumber = ActiveSheet.Range("F1").Value
x = InputBox("How many pages need to be printed", "How Many Pages?", "1")
If Val(x) < 1 Then Exit Sub
For UniqueCopies = 1 To x
ActiveSheet.Range("F1").Value = SequenceNumber
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
Next
End Sub



"lhsallwasser" wrote:


Hello:

I have a one page spreadsheet, essentially a template. I would like to
be able to print as many copies as needed, but each copy has to print a
unique sequence value, which increments by one every time it is
printed.

This variable may require some formatting, but it's somewhat like a
page number. Printing multiple copies is just reprinting page one to
Excel; each page is labeled 1. I'd rather not copy my print range x
number of times into the workbook, but that's what I did, just to get
started. I don't have any VB experience; I was just trying to use
Excel's existing functionality.

Thank you,

Laura Sallwasser


--
lhsallwasser

  #4   Report Post  
Ron de Bruin
 
Posts: n/a
Default

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"lhsallwasser" wrote in message ...

Hello:

I have a one page spreadsheet, essentially a template. I would like to
be able to print as many copies as needed, but each copy has to print a
unique sequence value, which increments by one every time it is
printed.

This variable may require some formatting, but it's somewhat like a
page number. Printing multiple copies is just reprinting page one to
Excel; each page is labeled 1. I'd rather not copy my print range x
number of times into the workbook, but that's what I did, just to get
started. I don't have any VB experience; I was just trying to use
Excel's existing functionality.

Thank you,

Laura Sallwasser


--
lhsallwasser



  #5   Report Post  
Junior Member
 
Posts: 3
Default

Roy, thank you so much for this solution. I will try it right away.

Best regards,

Laura Sallwasser


Quote:
Originally Posted by Roy Wagner
Laura,

If you want to use the center footer, replace the FOR/NEXT loop with the one
below. You still need a placeholder on the worksheet, but the sequence number
statement is printed in the footer rather than on the sheet (only visible in
preview etc.). You can eliminate the formula on the sheet that put the text
and number together. I also changed it so it increments the number before
printing instead of after. If you ran it twice the other way, you would see
that the last copy from 1st run would have the same number as the first copy
of the 2nd run.

Roy

For UniqueCopies = 1 To x
SequenceNumber = SequenceNumber + 1
ActiveSheet.Range("F1").Value = SequenceNumber
ActiveSheet.PageSetup.CenterFooter = "Sequence" & Str(SequenceNumber)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next



"Roy Wagner" wrote:

Laura,

Test this in a new workbook.

For testing I used cell F1 as the storage location of the sequence number.
You can put it anywhere you want, just change the cell reference in the code
from F1 to your range. You can format your sequence # cell white on white for
example so it cannot be seen. With a little more work, we could simply read
the existing sequence number listed on the sheet, break out the numeric
portion, increment it, put it back together and toss on the sheet, if you
really don't want a placeholder cell.

Where you have the Sequence ID printed on the bottom of your form, add this
formula with your own version of text.

="Sequence " & F1

This produces: "Sequence 1001" if cell F1 contains 1001. That's a simple
structure, but it can be fairly complex if you want to be more creative with
it.

Create a new macro called PrintSequence, give it a shortcut key "ctrl-p" and
replace the code generated with the code below. You can name the macro and
assign the short cut as you see fit.

Roy

Sub PrintSequence()
'
' PrintSequence Macro
' Macro recorded (altered) 8/16/2005 by Roy Wagner
'
' Keyboard Shortcut: Ctrl+p
'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies as Integer

SequenceNumber = ActiveSheet.Range("F1").Value
x = InputBox("How many pages need to be printed", "How Many Pages?", "1")
If Val(x) 1 Then Exit Sub
For UniqueCopies = 1 To x
ActiveSheet.Range("F1").Value = SequenceNumber
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
Next
End Sub



"lhsallwasser" wrote:


Hello:

I have a one page spreadsheet, essentially a template. I would like to
be able to print as many copies as needed, but each copy has to print a
unique sequence value, which increments by one every time it is
printed.

This variable may require some formatting, but it's somewhat like a
page number. Printing multiple copies is just reprinting page one to
Excel; each page is labeled 1. I'd rather not copy my print range x
number of times into the workbook, but that's what I did, just to get
started. I don't have any VB experience; I was just trying to use
Excel's existing functionality.

Thank you,

Laura Sallwasser


--
lhsallwasser
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Print multiple tabs on one page excelQ Excel Discussion (Misc queries) 2 July 7th 05 02:38 PM
print multiple copies of the same page with sequential numbers labrat Excel Discussion (Misc queries) 1 June 14th 05 05:32 PM
One spreadsheet cell won't print but shows up in print preview? Sally Excel Discussion (Misc queries) 1 May 26th 05 07:06 PM
Print ascending number on multiple printed cpoies cbwood Excel Discussion (Misc queries) 0 May 25th 05 11:08 PM
How can I print long lists of data from an Excel spreadsheet? sonic_d_hog Excel Discussion (Misc queries) 1 December 22nd 04 10:31 PM


All times are GMT +1. The time now is 05:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"