Trouble figuring out how to Loop through a huge repetitive macro

[Don M.]

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

 

[Susan]

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

 

[JLGWhiz]

You can fix the problem by modularizing your code into several sub routines.
Separate those parts of your code that perform a particular function like
sorting, do-loops, For Each loops, etc. into their own sub routines and then
call those routines from a master macro. For example:

Sub Master()
Range("A1") = Date
Call Macro1() 'Find data and copy to range
Call Macro2() 'Sort data range, copy to new sheet
Call Macro3() 'Add borders to range
Call Macro4() 'Page setup and print
ThisWorkbook.Close SaveChanges:=True
End Sub

This will allow all of your subroutines to run as one procedure, but it
clear the memory stack after each subroutine runs and avoids the overload.

 

[Gary''s Student]

Try changes like from:

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

to:

With Worksheets("Checks")
For i = 5 To 12
If .Cells(i, 148) = 1 Then
ActiveWindow.SelectedSheets.PrintOut From:=i - 2, To:=i - 2
End If
Next
End With


Apply a similar approach to other sections of "loop-able" code.

 

[Stefi]

I couldn't figure out what is the purpose of using variables

Region1TradeShow1Copies
Region1TradeShow2Copies
....
etc.

They take up values but I didn't see signs os using them in your macro.
Maybe you should specify the task in plain words!

Regards,
Stefi


„Don M.†ezt írta:

 

[JLGWhiz]

For example, I have one program that is about 4.5 meg in size that contains
19 sub routines and 17 forms which also contain event code. Haven't had any
memory or sizing problems. It might be easier if you draw a block diagram on
a piece of paper to show the steps you want the program to perform, then make
each step into its own macro.

 

[Don M.]

Does the Procedure Too Large error result from too much text in the macro or
this a memory space thing? I thought this was all from having too long a
macro all this time. Do I just have too many variables?

Don

 

[Susan]

AFAIK it's not a matter of too many variables, it's a matter of how
much text is in the macro/module. that's why JLGWhiz can have such a
large macro & it runs fine - he's got it broken up into many different
modules. if he tried to put it all in one macro and/or one module, it
would be too long.
if you break up your macro, as suggested, into different modules, you
should not encounter that error any longer.
best wishes!

susan

 

[Don M.]

Thank s to both of you. I kind of used a combination of everyone's
suggestions to fix the problem. I first figured out a way of looping through
the sequential print statements I have to have which cut down the number of
lines significantly. This got rid of the error for now. however, this is a
work in progress and will likely get much bigger. In that case, I will break
the macro apart.

For my own reference, what are the limitations for the size of the macro? I
tried copying the macro into Word and counted the characters and lines and
such, but did not see where the macro that was too long failed and the one
that I ended up with passed.

Don

 

[Susan]

i don't know what the limits are.............
sorry!
susan

 

[shg]

For my own reference, what are the limitations for the size of th
macro?

According to Stephen Bullen, there is no hard limit, but when the siz
of the module (exported as whatever.bas) exceeds 64K, things get dicey.

I've had success using CodeCleaner periodically ('VBA Code Cleaner
(http://www.appspro.com/Utilities/CodeCleaner.htm)) in resolvin
corruption problems in workbooks large and small. It does the same thin
you can do manually: Export and remove all the modules, save and clos
the workbook, reopen, and restore the modules

 

[JLGWhiz]

I'm wondering if that message has to do with how much the compiler can handle
without dumping. I don't believe it is a data storage problem, but more
related to how many variables have to be tracked while compiling. Otherwise
the message would have been out of memory or stack overflow.

 

[JLGWhiz]

I finally found it. I knew it was out there somewhere.

PRB: Procedure too Large Error When Compiling in VB 4.0
View products that this article applies to.
Article ID : 129897
Last Review : December 9, 2003
Revision : 2.0
This article was previously published under Q129897
SYMPTOMS
Some procedures that compile in Visual Basic version 3.0 generate the
following compile-time error message in Visual Basic version 4.0:
Procedure too large

Back to the top

CAUSE
The maximum size of the internal code for an individual procedure in Visual
Basic version 3.0 is 64K bytes. Microsoft Visual Basic version 4.0 for
Windows uses Microsoft Visual Basic for Applications, which generates more
generic internal code than does Microsoft Visual Basic version 3.0. This
generic code allows for compatibility with a wider range of processors. This
makes the internal code larger, so it can cause very large procedures to
exceed the 64K limit and fail to compile.
Back to the top

WORKAROUND
Split the large procedure into two or more smaller procedures.
Back to the top

STATUS
This behavior is by design.