+ Reply to Thread
Results 1 to 4 of 4

How do I print a one page spreadsheet multiple time, each with its own page

  1. #1
    lhsallwasser
    Guest

    How do I print a one page spreadsheet multiple time, each with its own page


    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

  2. #2
    Roy Wagner
    Guest

    RE: How do I print a one page spreadsheet multiple time, each with its

    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. #3
    Roy Wagner
    Guest

    RE: How do I print a one page spreadsheet multiple time, each with

    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. #4
    Ron de Bruin
    Guest

    Re: How do I print a one page spreadsheet multiple time, each with its own page number?

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

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


    "lhsallwasser" <[email protected]> wrote in message news:[email protected]...
    >
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1