I hope someone can help because this is driving me absolutely bonkers. This should be something so easy yet....

I am trying to print a number of pages depending on a cell value which is a value of a formulae.

The whole worksheet has page breaks in it to make 40 pages. Every time the above macro is run it will print 40 pages regardless of the value of (a).

What am I doing wrong?

Thanks

On 9/05/2012 1:06 AM, antonywalsh wrote:
I hope someone can help because this is driving me absolutely bonkers.
This should be something so easy yet....

I am trying to print a number of pages depending on a cell value which
is a value of a formulae.


Code:
--------------------
Sub PRINT_GENERIC()

Sheets("GENERIC").Select
a = Range("Z1").Value
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=a, Copies:=1
Sheets("MAINSHEET").Select

End Sub
--------------------


The whole worksheet has page breaks in it to make 40 pages. Every time
the above macro is run it will print 40 pages regardless of the value of
(a).

What am I doing wrong?

Thanks




You could try.

Sub PRINT_GENERIC()

Dim Gsht as worksheet, Msht as Worksheet
Dim a as Range

Set Gsht = Sheets("GENRERIC")
Set Msht = Sheets("MAINSHEET")
Set a = [Z1]

Gsht.PrintOut From:=1, To:=a, Copies:=1

Msht.Activate

End Sub

HTH
Mick.

I was having a further think about this and considered that it may be
useful to return [Z1].value to zero once the print has been passed to
the printer and you have returned to the Main Sheet, this would force
user to select their required amounts eliminating un-necessary waste
should the user forget to change down a higher amount.

Also I put an error message in it that alerts the user they need to
enter a value of 1 to 40 in the event they leave the cell with zero(0)

This code has been tested and you can copy/paste directly as I copied it
directly from my VBE window.

HTH
Mick.

Sub PRINT_GENERIC()

Dim Gsht As Worksheet, Msht As Worksheet
Dim a As Range
Dim Oops As Integer

Set Gsht = Sheets("GENERIC")
Set Msht = Sheets("MAINSHEET")
Set a = [Z1]

If a.Value 0 Then
Gsht.PrintOut From:=1, To:=a, Copies:=1
Else
Oops = MsgBox("You must enter a value between 1 & 40", vbOKOnly)
Exit Sub
End If

a.Value = 0

Msht.Activate

End Sub

Mick,
Since vbOkOnly is the default button for MsgBox, you don't need to code
for it. Specifying button[s] is only necessary if you want others than
the default!<g

I might suggest (if I may) that you could also implement some way of
not hard-coding the To:= parameter. Perhaps the user could just select
the last row and your code could use that instead...

To:=Selection.Row

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion