Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA print x number of copies based on number specified in a cell

  1. #1
    Board Regular
    Join Date
    Mar 2009
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA print x number of copies based on number specified in a cell

    I have two worksheets that we print off weekly. However, the quantity of pages we print each week changes. I would like to have a cell that asks for the number of copies to print, then after my existing script runs through changes on the sheet, it'll automatically print x number of copies that was specified in that cell. Any ideas?

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,229
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA print x number of copies based on number specified in a cell

    Should be easy if you just add one line to your code:

    Code:
    ActiveSheet.Printout  Copies:=Range("A1").Value
    Or
    Code:
    For x = 1 to Range("A1").Value
        ActiveSheet.Printout
    Next x
    I suppose you may want to add some validation - is A1 a number, is it greater than 0? Is it less than a maximum allowed number? Or have the number of copies entered at runtime with an input box.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,228
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA print x number of copies based on number specified in a cell

    Hi there,

    Run the following on the sheet in question. It will print that sheet the number of times that is in cell A2 (change as required):

    Code:
    Sub Macro1()
    
        With ActiveSheet
            .PrintOut Copies:=Range("A2").Value
        End With
        
    End Sub
    HTH

    Robert

  4. #4
    Board Regular
    Join Date
    Mar 2009
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA print x number of copies based on number specified in a cell

    Code:
    Sub Macro1()
    
        With ActiveSheet
            .PrintOut Copies:=Range("A2").Value
        End With
        
    End Sub
    HTH

    Robert[/QUOTE]


    I get a runtime error 1004 when I add this to the end of my other script. If I run it as a separate macro, it works. Why would that be?

  5. #5
    Board Regular
    Join Date
    Mar 2009
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA print x number of copies based on number specified in a cell

    AH! I got it. Had to add to select the sheet I needed. THANKS!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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