| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble figuring out how to Loop through a huge repetitive macro
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive macro
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. "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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive macro
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. -- Gary''s Student - gsnu200811 "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) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive macro
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: 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) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive macro
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." 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) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive mac
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive mac
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 On Nov 3, 12:52*pm, Don M. wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive mac
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" wrote: 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 On Nov 3, 12:52 pm, Don M. wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive mac
i don't know what the limits are.............
sorry! susan On Nov 3, 3:04*pm, Don M. wrote: 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" wrote: 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 On Nov 3, 12:52 pm, Don M. wrote: 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- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive macro
For my own reference, what are the limitations for the size of the macro? According to Stephen Bullen, there is no hard limit, but when the size 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 resolving corruption problems in workbooks large and small. It does the same thing you can do manually: Export and remove all the modules, save and close the workbook, reopen, and restore the modules. -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24444 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive mac
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. "shg" wrote: For my own reference, what are the limitations for the size of the macro? According to Stephen Bullen, there is no hard limit, but when the size 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 resolving corruption problems in workbooks large and small. It does the same thing you can do manually: Export and remove all the modules, save and close the workbook, reopen, and restore the modules. -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24444 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble figuring out how to Loop through a huge repetitive macro
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. "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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| repetitive items in For each loop | Excel Programming | |||
| Stopping repetitive loop execution through user form (or other ide | Excel Programming | |||
| Convert repetitive IF statements to array loop? | Excel Programming | |||
| Figuring out loop for a production schedule | Excel Discussion (Misc queries) | |||
| Macro -- repetitive tasks | Excel Discussion (Misc queries) | |||
Linear Mode
