Can I shorten this?

[TyeJae]

Is there a way to shorten this so that I do not have to type so muc
out? Because I have a couple cells I need it to read with differen
From and To's. And it would be a very long VBA.


Private Sub CommandButton1_Click()
Select Case Range("Quanity").Value
Case 1
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Collate _
:=True
Case 2
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=2
Collate _
:=True
Case 3
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=3
Collate _
:=True
Case 4
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=4
Collate _
:=True
Case 5
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=5
Collate _
:=True
Case 6
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=6
Collate _
:=True
Case 7
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=7
Collate _
:=True
Case 8
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=8
Collate _
:=True
Case 9
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=9
Collate _
:=True
Case 10
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=10
Collate _
:=True
Case 11
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=11
Collate _
:=True
Case 12
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=12
Collate _
:=True
End Select
End Sub

Thanks in advance,
TyeJa

 

[JE McGimpsey]

One way:

ActiveWindow.SelectedSheets.Printout _
From:=1, _
To:=1, _
Copies:=Range("Quantity").Value, _
Collate:=True

 

[talkswithnumber]

Hi TyeJae,

You should be able to use the range value more directly since the valu
corresponds exactly, rather than going to the trouble of the Selec
Case:

Dim numCopies as integer
numCopies = Range("Quanity").Value
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=numCopies
_Collate:=Tru

 

[TyeJae]

It says it is having a syntax error when I try that formula...an
suggestions?

Thanks,
TyeJa

 

[TyeJae]

nm I figured it out, you had _Collate when it should be Collate_. Tha
works GREAT and will save me a ton of time...thank you.

TyeJa

 

[talkswithnumber]

Sorry, I copied the line from your post but didn't realize the _ wa
still there after I added the numCopies bit. Glad it worked ou
though..