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

[chelseasikoebs]

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?

 

[xenou]

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

ActiveSheet.Printout  Copies:=Range("A1").Value

Or

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.

 

[Trebor76]

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):

Sub Macro1()

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

HTH

Robert

 

[chelseasikoebs]

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?

 

[chelseasikoebs]

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