Variable printarea (resolved)

[opopanax666]

Hi guys & gals,

Here's 'my' code :

Private Sub CommandButton1_Click()
Dim MyValue As Integer
Sheets("Blad1").Select
MyValue = Range("ab1").Value
Range("$A$1:$Z$" & MyValue).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$Z$" & MyValue
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Registers").Select
End Sub

So, the button is placed on sheet "Registers", the area to print on "Blad1". I'm using a formula to give me the # of rows to print in cell AB1 on "Blad1" (can only be integer, btw).
I'm new to VB, but this ought to be pretty straightforward, so why does it give me an error in "Range("$A$1:$Z$" & MyValue).Select"?

Thank you for any feedback.

 

[Andrew Poulsom]

Your code worked for me, but you could try removing the $ signs and ensuring that the CommandButton's TakeFocusOnClick property is False.

 

[opopanax666]

Did the above, but now I get a 1004 error :

Method Range of object_Worksheet failed...

 

[Andrew Poulsom]

What is your code now?

 

[opopanax666]

Private Sub CommandButton1_Click()
Dim MyValue As Integer
Sheets("Blad1").Select
MyValue = Range("ab1").Value
Range("A1:Z" & MyValue).Select
ActiveSheet.PageSetup.PrintArea = "A1:Z" & MyValue
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Registers").Select
End Sub

And I set the TakeFocusOnClick flag to False
-----------------------------------------------------
In the meantime someone over at VBforums came up with this solution, and it did the trick :

Private Sub CommandButton1_Click()
Dim MyValue As Integer
MyValue = Sheets("Blad1").Range("ab1").value
Sheets("Blad1").PageSetup.PrintArea = "A1:Z" & MyValue
Sheets("Blad1").PrintOut Copies:=1, Collate:=True
End Sub

Thanks very much for the feedback!