Thread: Trouble figuring out how to Loop through a huge repetitive macro
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Susan Susan is offline
external usenet poster
  
Posts: 1,117
Default Trouble figuring out how to Loop through a huge repetitive macro
one simple suggestion would be to put each region in it's own module.
then, in the master macro, simply call each macro.... put all the
coding between the if statements into each individual module. that
would cut down on the procedure error. as for actually reducing the
amount of coding, you're way beyond me...............

'==========================
Sub Print()

If Worksheets("Checks").Cells(2, 144) = 0 Then GoTo Region2:
Call Region_1_Macro
End If

Region2:

If Worksheets("Checks").Cells(2, 146) = 0 Then GoTo Region3:
Call Region_2_Macro
End If

'=================================
etc.
hope that helps somewhat.
:)
susan




On Nov 3, 8:35*am, Don M. wrote:
I've written this huge macro to print out a large series of pages
selectively. It's gotten so big that when Itry to run it I get the Procedure
Too Large error. There is lots of repetition in the macro and I can see that
a bunch of nested loops is what I need. I've been chasing my tail for a week
trying to figure out how to get it too work correctly. I've tried For Next,
Do Loop, arrayed variables. I'm just not experienced enough for either of
these techniques to nail it down. If I weren't already bald I'd pull my hair
out. Basically, the first 87 lines just repeat themselves 9 times. I'm jusat
stuck trying to keep the variables straight and in order.

There are 9 regions in all. In line 3, with Cells(2,144) will go from 144 to
160 by 2's and that 2 will go from 2 to 29. Line 5 through 19 have Cells(17,
3), Cells(18, 3), Cells(19, 3), etc. That 17 will go to 28 and the 3 will go
from 3 to 19 by 2's. The variable "Publication" gets set earlier in the macro
and stays constant throughout. The 105 in Line 18 stays constant.

I hope if you can cut and paste this whole message into Word it will
reconnect the broken lines for you. Here's the first four regions of the
macro. The last five are simply the next iterations of the Region variable.

Sub Print()

If Worksheets("Checks").Cells(2, 144) = 0 Then GoTo Region2:

Region1TradeShow1Copies = Worksheets("Run Report").Cells(17, 3)
Region1TradeShow2Copies = Worksheets("Run Report").Cells(18, 3)
Region1TradeShow3Copies = Worksheets("Run Report").Cells(19, 3)
Region1TradeShow4Copies = Worksheets("Run Report").Cells(20, 3)
Region1TradeShow5Copies = Worksheets("Run Report").Cells(21, 3)
Region1TradeShow6Copies = Worksheets("Run Report").Cells(22, 3)
Region1TradeShow7Copies = Worksheets("Run Report").Cells(23, 3)
Region1TradeShow8Copies = Worksheets("Run Report").Cells(24, 3)
Region1TradeShow9Copies = Worksheets("Run Report").Cells(25, 3)
Region1TradeShow10Copies = Worksheets("Run Report").Cells(26, 3)
Region1Auction1Copies = Worksheets("Run Report").Cells(27, 3)
Region1Auction2Copies = Worksheets("Run Report").Cells(28, 3)
Region1ShowCopiesPerBox = Worksheets("Run Report").Cells(105, 3)
Region1TradeShow1Tags = (Int(Region1TradeShow1Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow2Tags = (Int(Region1TradeShow2Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow3Tags = (Int(Region1TradeShow3Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow4Tags = (Int(Region1TradeShow4Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow5Tags = (Int(Region1TradeShow5Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow6Tags = (Int(Region1TradeShow6Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow7Tags = (Int(Region1TradeShow7Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow8Tags = (Int(Region1TradeShow8Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow9Tags = (Int(Region1TradeShow9Copies) /
Region1ShowCopiesPerBox)
Region1TradeShow10Tags = (Int(Region1TradeShow10Copies) /
Region1ShowCopiesPerBox)
Region1Auction1Tags = (Int(Region1Auction1Copies) / Region1ShowCopiesPerBox)
Region1Auction2Tags = (Int(Region1Auction2Copies) / Region1ShowCopiesPerBox)

Region1MailTags = Worksheets("Run Report").Cells(121, 3)

Sheets("Region1 Run").Select
If Worksheets("Checks").Cells(3, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1
If Worksheets("Checks").Cells(4, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2

Sheets("Region1 Work Order").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1

Sheets("Region1 Run").Select
If Worksheets("Checks").Cells(5, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=3, to:=3
If Worksheets("Checks").Cells(6, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=4, to:=4
If Worksheets("Checks").Cells(7, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=5, to:=5
If Worksheets("Checks").Cells(8, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=6, to:=6
If Worksheets("Checks").Cells(9, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=7, to:=7
If Worksheets("Checks").Cells(10, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=8, to:=8
If Worksheets("Checks").Cells(11, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=9, to:=9
If Worksheets("Checks").Cells(12, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=10, to:=10
If Worksheets("Checks").Cells(13, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=11, to:=11,
Copies:=Region1TradeShow1Tags *
If Worksheets("Checks").Cells(14, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=12, to:=12,
Copies:=Region1TradeShow2Tags
If Worksheets("Checks").Cells(15, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=13, to:=13,
Copies:=Region1TradeShow3Tags
If Worksheets("Checks").Cells(16, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=14, to:=14,
Copies:=Region1TradeShow4Tags
If Worksheets("Checks").Cells(17, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=15, to:=15,
Copies:=Region1TradeShow5Tags
If Worksheets("Checks").Cells(18, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=16,
Copies:=Region1TradeShow6Tags
If Worksheets("Checks").Cells(19, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=17, to:=17,
Copies:=Region1TradeShow7Tags
If Worksheets("Checks").Cells(20, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=18, to:=18,
Copies:=Region1TradeShow8Tags
If Worksheets("Checks").Cells(21, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=19, to:=19,
Copies:=Region1TradeShow9Tags
If Worksheets("Checks").Cells(22, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=20, to:=20,
Copies:=Region1TradeShow10Tags
If Worksheets("Checks").Cells(23, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=21, to:=21,
Copies:=Region1Auction1Tags
If Worksheets("Checks").Cells(24, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=22, to:=22,
Copies:=Region1Auction1Tags

Sheets("Region1 Box").Select
If Worksheets("Checks").Cells(26, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut 'Box Report

Sheets("Region1 Pallet Tags").Select
If Worksheets("Checks").Cells(27, 144) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=Region1MailTags 'Mail
Pallet Tags

Sheets("Region1 Bulk Tags").Select
If Worksheets("Checks").Cells(25, 144) < 1 Then GoTo Region2:

If Publication = "Truck Paper" Then 'Truck Paper
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 144) < 1 Then GoTo Region2:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 144) < 1 Then GoTo Region2:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=26

ElseIf Publication = "Tractor House" Then 'Tractor House
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 144) < 1 Then GoTo Region2:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 144) < 1 Then GoTo Region2:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=32

End If

Region2:

If Worksheets("Checks").Cells(2, 146) = 0 Then GoTo Region3:

Region2TradeShow1Copies = Worksheets("Run Report").Cells(17, 5)
Region2TradeShow2Copies = Worksheets("Run Report").Cells(18, 5)
Region2TradeShow3Copies = Worksheets("Run Report").Cells(19, 5)
Region2TradeShow4Copies = Worksheets("Run Report").Cells(20, 5)
Region2TradeShow5Copies = Worksheets("Run Report").Cells(21, 5)
Region2TradeShow6Copies = Worksheets("Run Report").Cells(22, 5)
Region2TradeShow7Copies = Worksheets("Run Report").Cells(23, 5)
Region2TradeShow8Copies = Worksheets("Run Report").Cells(24, 5)
Region2TradeShow9Copies = Worksheets("Run Report").Cells(25, 5)
Region2TradeShow10Copies = Worksheets("Run Report").Cells(26, 5)
Region2Auction1Copies = Worksheets("Run Report").Cells(27, 5)
Region2Auction2Copies = Worksheets("Run Report").Cells(28, 5)

Region2ShowCopiesPerBox = Worksheets("Run Report").Cells(105, 5)

Region2TradeShow1Tags = (Int(Region2TradeShow1Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow2Tags = (Int(Region2TradeShow2Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow3Tags = (Int(Region2TradeShow3Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow4Tags = (Int(Region2TradeShow4Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow5Tags = (Int(Region2TradeShow5Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow6Tags = (Int(Region2TradeShow6Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow7Tags = (Int(Region2TradeShow7Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow8Tags = (Int(Region2TradeShow8Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow9Tags = (Int(Region2TradeShow9Copies) /
Region2ShowCopiesPerBox)
Region2TradeShow10Tags = (Int(Region2TradeShow10Copies) /
Region2ShowCopiesPerBox)
Region2Auction1Tags = (Int(Region2Auction1Copies) / Region2ShowCopiesPerBox)
Region2Auction2Tags = (Int(Region2Auction2Copies) / Region2ShowCopiesPerBox)

Region2MailTags = Worksheets("Run Report").Cells(121, 5)

Sheets("Region2 Run").Select
If Worksheets("Checks").Cells(3, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1
If Worksheets("Checks").Cells(4, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2

Sheets("Region2 Work Order").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1

Sheets("Region2 Run").Select
If Worksheets("Checks").Cells(5, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=3, to:=3
If Worksheets("Checks").Cells(6, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=4, to:=4
If Worksheets("Checks").Cells(7, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=5, to:=5
If Worksheets("Checks").Cells(8, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=6, to:=6
If Worksheets("Checks").Cells(9, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=7, to:=7
If Worksheets("Checks").Cells(10, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=8, to:=8
If Worksheets("Checks").Cells(11, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=9, to:=9
If Worksheets("Checks").Cells(12, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=10, to:=10
If Worksheets("Checks").Cells(13, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=11, to:=11,
Copies:=Region2TradeShow1Tags
If Worksheets("Checks").Cells(14, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=12, to:=12,
Copies:=Region2TradeShow2Tags
If Worksheets("Checks").Cells(15, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=13, to:=13,
Copies:=Region2TradeShow3Tags
If Worksheets("Checks").Cells(16, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=14, to:=14,
Copies:=Region2TradeShow4Tags
If Worksheets("Checks").Cells(17, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=15, to:=15,
Copies:=Region2TradeShow5Tags
If Worksheets("Checks").Cells(18, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=16,
Copies:=Region2TradeShow6Tags
If Worksheets("Checks").Cells(19, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=17, to:=17,
Copies:=Region2TradeShow7Tags
If Worksheets("Checks").Cells(20, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=18, to:=18,
Copies:=Region2TradeShow8Tags
If Worksheets("Checks").Cells(21, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=19, to:=19,
Copies:=Region2TradeShow9Tags
If Worksheets("Checks").Cells(22, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=20, to:=20,
Copies:=Region2TradeShow10Tags0
If Worksheets("Checks").Cells(23, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=21, to:=21,
Copies:=Region2Auction1Tags
If Worksheets("Checks").Cells(24, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=22, to:=22,
Copies:=Region2Auction1Tags

Sheets("Region2 Box").Select
If Worksheets("Checks").Cells(26, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut 'Box Report

Sheets("Region2 Pallet Tags").Select
If Worksheets("Checks").Cells(27, 146) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=Region2MailTags 'Mail
Pallet Tags

Sheets("Region2 Bulk Tags").Select
If Worksheets("Checks").Cells(25, 146) < 1 Then GoTo Region3:

If Publication = "Truck Paper" Then 'Truck Paper
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 146) < 1 Then GoTo Region3:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 146) < 1 Then GoTo Region3:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=26

ElseIf Publication = "Tractor House" Then 'Tractor House
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 146) < 1 Then GoTo Region3:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 146) < 1 Then GoTo Region3:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=32

End If

Region3:

If Worksheets("Checks").Cells(2, 148) = 0 Then GoTo Region4:

Region3TradeShow1Copies = Worksheets("Run Report").Cells(17, 7)
Region3TradeShow2Copies = Worksheets("Run Report").Cells(18, 7)
Region3TradeShow3Copies = Worksheets("Run Report").Cells(19, 7)
Region3TradeShow4Copies = Worksheets("Run Report").Cells(20, 7)
Region3TradeShow5Copies = Worksheets("Run Report").Cells(21, 7)
Region3TradeShow6Copies = Worksheets("Run Report").Cells(22, 7)
Region3TradeShow7Copies = Worksheets("Run Report").Cells(23, 7)
Region3TradeShow8Copies = Worksheets("Run Report").Cells(24, 7)
Region3TradeShow9Copies = Worksheets("Run Report").Cells(25, 7)
Region3TradeShow10Copies = Worksheets("Run Report").Cells(26, 7)
Region3Auction1Copies = Worksheets("Run Report").Cells(27, 7)
Region3Auction2Copies = Worksheets("Run Report").Cells(28, 7)

Region3ShowCopiesPerBox = Worksheets("Run Report").Cells(105, 7)

Region3TradeShow1Tags = (Int(Region3TradeShow1Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow2Tags = (Int(Region3TradeShow2Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow3Tags = (Int(Region3TradeShow3Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow4Tags = (Int(Region3TradeShow4Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow5Tags = (Int(Region3TradeShow5Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow6Tags = (Int(Region3TradeShow6Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow7Tags = (Int(Region3TradeShow7Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow8Tags = (Int(Region3TradeShow8Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow9Tags = (Int(Region3TradeShow9Copies) /
Region3ShowCopiesPerBox)
Region3TradeShow10Tags = (Int(Region3TradeShow10Copies) /
Region3ShowCopiesPerBox)
Region3Auction1Tags = (Int(Region3Auction1Copies) / Region3ShowCopiesPerBox)
Region3Auction2Tags = (Int(Region3Auction2Copies) / Region3ShowCopiesPerBox)

Region3MailTags = Worksheets("Run Report").Cells(121, 7)

Sheets("Region3 Run").Select
If Worksheets("Checks").Cells(3, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1
If Worksheets("Checks").Cells(4, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2

Sheets("Region3 Work Order").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1

Sheets("Region3 Run").Select
If Worksheets("Checks").Cells(5, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=3, to:=3
If Worksheets("Checks").Cells(6, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=4, to:=4
If Worksheets("Checks").Cells(7, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=5, to:=5
If Worksheets("Checks").Cells(8, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=6, to:=6
If Worksheets("Checks").Cells(9, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=7, to:=7
If Worksheets("Checks").Cells(10, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=8, to:=8
If Worksheets("Checks").Cells(11, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=9, to:=9
If Worksheets("Checks").Cells(12, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=10, to:=10
If Worksheets("Checks").Cells(13, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=11, to:=11,
Copies:=Region3TradeShow1Tags
If Worksheets("Checks").Cells(14, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=12, to:=12,
Copies:=Region3TradeShow2Tags
If Worksheets("Checks").Cells(15, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=13, to:=13,
Copies:=Region3TradeShow3Tags
If Worksheets("Checks").Cells(16, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=14, to:=14,
Copies:=Region3TradeShow4Tags
If Worksheets("Checks").Cells(17, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=15, to:=15,
Copies:=Region3TradeShow5Tags
If Worksheets("Checks").Cells(18, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=16,
Copies:=Region3TradeShow6Tags
If Worksheets("Checks").Cells(19, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=17, to:=17,
Copies:=Region3TradeShow7Tags
If Worksheets("Checks").Cells(20, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=18, to:=18,
Copies:=Region3TradeShow8Tags
If Worksheets("Checks").Cells(21, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=19, to:=19,
Copies:=Region3TradeShow9Tags
If Worksheets("Checks").Cells(22, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=20, to:=20,
Copies:=Region3TradeShow10Tags0
If Worksheets("Checks").Cells(23, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=21, to:=21,
Copies:=Region3Auction1Tags
If Worksheets("Checks").Cells(24, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=22, to:=22,
Copies:=Region3Auction1Tags

Sheets("Region3 Box").Select
If Worksheets("Checks").Cells(26, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut 'Box Report

Sheets("Region3 Pallet Tags").Select
If Worksheets("Checks").Cells(27, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=Region3MailTags 'Mail
Pallet Tags

Sheets("Region3 Bulk Tags").Select
If Worksheets("Checks").Cells(25, 148) < 1 Then GoTo Region4:

If Publication = "Truck Paper" Then 'Truck Paper
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 148) < 1 Then GoTo Region4:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 148) < 1 Then GoTo Region4:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=26

ElseIf Publication = "Tractor House" Then 'Tractor House
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 148) < 1 Then GoTo Region4:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 148) < 1 Then GoTo Region4:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=32

End If

Region4:

If Worksheets("Checks").Cells(2, 150) = 0 Then GoTo Region5:

Region4TradeShow1Copies = Worksheets("Run Report").Cells(17, 9)
Region4TradeShow2Copies = Worksheets("Run Report").Cells(18, 9)
Region4TradeShow3Copies = Worksheets("Run Report").Cells(19, 9)
Region4TradeShow4Copies = Worksheets("Run Report").Cells(20, 9)
Region4TradeShow5Copies = Worksheets("Run Report").Cells(21, 9)
Region4TradeShow6Copies = Worksheets("Run Report").Cells(22, 9)
Region4TradeShow7Copies = Worksheets("Run Report").Cells(23, 9)
Region4TradeShow8Copies = Worksheets("Run Report").Cells(24, 9)
Region4TradeShow9Copies = Worksheets("Run Report").Cells(25, 9)
Region4TradeShow10Copies = Worksheets("Run Report").Cells(26, 9)
Region4Auction1Copies = Worksheets("Run Report").Cells(27, 9)
Region4Auction2Copies = Worksheets("Run Report").Cells(28, 9)

Region4ShowCopiesPerBox = Worksheets("Run Report").Cells(105, 9)

Region4TradeShow1Tags = (Int(Region4TradeShow1Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow2Tags = (Int(Region4TradeShow2Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow3Tags = (Int(Region4TradeShow3Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow4Tags = (Int(Region4TradeShow4Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow5Tags = (Int(Region4TradeShow5Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow6Tags = (Int(Region4TradeShow6Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow7Tags = (Int(Region4TradeShow7Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow8Tags = (Int(Region4TradeShow8Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow9Tags = (Int(Region4TradeShow9Copies) /
Region4ShowCopiesPerBox)
Region4TradeShow10Tags = (Int(Region4TradeShow10Copies) /
Region4ShowCopiesPerBox)
Region4Auction1Tags = (Int(Region4Auction1Copies) / Region4ShowCopiesPerBox)
Region4Auction2Tags = (Int(Region4Auction2Copies) / Region4ShowCopiesPerBox)

Region4MailTags = Worksheets("Run Report").Cells(121, 9)

Sheets("Region4 Run").Select
If Worksheets("Checks").Cells(3, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1
If Worksheets("Checks").Cells(4, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2

Sheets("Region4 Work Order").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=1

Sheets("Region4 Run").Select
If Worksheets("Checks").Cells(5, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=3, to:=3
If Worksheets("Checks").Cells(6, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=4, to:=4
If Worksheets("Checks").Cells(7, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=5, to:=5
If Worksheets("Checks").Cells(8, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=6, to:=6
If Worksheets("Checks").Cells(9, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=7, to:=7
If Worksheets("Checks").Cells(10, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=8, to:=8
If Worksheets("Checks").Cells(11, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=9, to:=9
If Worksheets("Checks").Cells(12, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=10, to:=10
If Worksheets("Checks").Cells(13, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=11, to:=11,
Copies:=Region4TradeShow1Tags
If Worksheets("Checks").Cells(14, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=12, to:=12,
Copies:=Region4TradeShow2Tags
If Worksheets("Checks").Cells(15, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=13, to:=13,
Copies:=Region4TradeShow3Tags
If Worksheets("Checks").Cells(16, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=14, to:=14,
Copies:=Region4TradeShow4Tags
If Worksheets("Checks").Cells(17, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=15, to:=15,
Copies:=Region4TradeShow5Tags
If Worksheets("Checks").Cells(18, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=16,
Copies:=Region4TradeShow6Tags
If Worksheets("Checks").Cells(19, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=17, to:=17,
Copies:=Region4TradeShow7Tags
If Worksheets("Checks").Cells(20, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=18, to:=18,
Copies:=Region4TradeShow8Tags
If Worksheets("Checks").Cells(21, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=19, to:=19,
Copies:=Region4TradeShow9Tags
If Worksheets("Checks").Cells(22, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=20, to:=20,
Copies:=Region4TradeShow10Tags0
If Worksheets("Checks").Cells(23, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=21, to:=21,
Copies:=Region4Auction1Tags
If Worksheets("Checks").Cells(24, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=22, to:=22,
Copies:=Region4Auction1Tags

Sheets("Region4 Box").Select
If Worksheets("Checks").Cells(26, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut 'Box Report

Sheets("Region4 Pallet Tags").Select
If Worksheets("Checks").Cells(27, 150) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=Region4MailTags 'Mail
Pallet Tags

Sheets("Region4 Bulk Tags").Select
If Worksheets("Checks").Cells(25, 150) < 1 Then GoTo Region5:

If Publication = "Truck Paper" Then 'Truck Paper
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 150) < 1 Then GoTo Region5:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 150) < 1 Then GoTo Region5:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=26

ElseIf Publication = "Tractor House" Then 'Tractor House
ActiveWindow.SelectedSheets.PrintOut From:=1, to:=15
If Worksheets("Checks").Cells(28, 150) < 1 Then GoTo Region5:
ActiveWindow.SelectedSheets.PrintOut From:=16, to:=25
If Worksheets("Checks").Cells(29, 150) < 1 Then GoTo Region5:
ActiveWindow.SelectedSheets.PrintOut From:=26, to:=32

End If

Reply With QuoteReply With Quote