Can I shorten this?
 

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

--
TyeJa
-----------------------------------------------------------------------
TyeJae's Profile: http://www.excelforum.com/member.php...nfo&userid=723
View this thread: http://www.excelforum.com/showthread.php?threadid=32030

Can I shorten this?
 

One way:

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

In article ,
TyeJae wrote:

Is there a way to shorten this so that I do not have to type so much
out? Because I have a couple cells I need it to read with different
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

Can I shorten this?
 

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

--
talkswithnumbe
-----------------------------------------------------------------------
talkswithnumber's Profile: http://www.excelforum.com/member.php...fo&userid=1684
View this thread: http://www.excelforum.com/showthread.php?threadid=32030

Can I shorten this?
 

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

Thanks,
TyeJa

--
TyeJa
-----------------------------------------------------------------------
TyeJae's Profile: http://www.excelforum.com/member.php...nfo&userid=723
View this thread: http://www.excelforum.com/showthread.php?threadid=32030

Can I shorten this?
 

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

--
TyeJa
-----------------------------------------------------------------------
TyeJae's Profile: http://www.excelforum.com/member.php...nfo&userid=723
View this thread: http://www.excelforum.com/showthread.php?threadid=32030

Can I shorten this?
 

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..

--
talkswithnumbe
-----------------------------------------------------------------------
talkswithnumber's Profile: http://www.excelforum.com/member.php...fo&userid=1684
View this thread: http://www.excelforum.com/showthread.php?threadid=32030