A simple printing pages depending on cell value

[antonywalsh]

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

[VBA]Sub PRINT_GENERIC()

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

End Sub[/VBA]

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

[Tinbendr]

Probably because the range isn't selected.

Are you sure Z1 has a value?

[vba]Sub PRINT_GENERIC()
Dim A As Long

With Sheets("GENERIC")
A = .Range("Z1").Value

If A > 0 Then
.PrintOut From:=1, To:=A, Copies:=1
Else
MsgBox ("Nothing to print!")
End If
End With
End Sub
[/vba]

[antonywalsh]

Tinbendr

Thank you for your reply, I tried this and it still does the same thing. Z1 definately has a value in it because I set it to be at least 1.

I don't know if this helps but I set my default printer to PDF and it works but obviously sends the worksheet to a PDF. This will only work if the user has a PDFcreator e.g. on their system.

I feel that the printer properties are not working correctly that its not recognising the value of A and printing number of pages. Even if its change to print 1 to 1 and not variable A it still prints 40 pages.

Can you help, my hair is very thin now.

Regards

[Tinbendr]

Hmmm, don't know.

Is the Print Range set in Excel?

Here is Ron's help page on printing, but I didn't see anything we're not doing.

We might try:
[vba]Sub PRINT_GENERIC()
Dim A As Long
Dim B As long

With Sheets("GENERIC")
A = .Range("Z1").Value

If A > 0 Then
For B = 1 To A
.PrintOut From:=B, to:=B, Copies:=1
Next
Else
MsgBox ("Nothing to print!")
End If
End With
End Sub
[/vba]
You might try recording a macro printing, say, page 1-3 and see if we're missing something.