Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Roy, thank you so much for this solution. I will try it right away.
Best regards, Laura Sallwasser Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print multiple tabs on one page | Excel Discussion (Misc queries) | |||
print multiple copies of the same page with sequential numbers | Excel Discussion (Misc queries) | |||
One spreadsheet cell won't print but shows up in print preview? | Excel Discussion (Misc queries) | |||
Print ascending number on multiple printed cpoies | Excel Discussion (Misc queries) | |||
How can I print long lists of data from an Excel spreadsheet? | Excel Discussion (Misc queries) |