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

Best regards,

Laura Sallwasser


Roy Wagner Wrote:
> 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
> --


--
lhsallwasser