Trying to get a macro to set up a page areas for over 100 pages in one sheet but not working?

[tonywatsonhelp]

Hi Everyone,

Ok so I have a bit of a problem and hope someone can help me?

First let me explain the setup,

I Have to produce a PDF the has many pages

(Over 200) ​

of different sizes (number of rows)
I have set this up manually using Print area and adjusting where the page breaks are but I need it to work with a PDF?

I have Identified the Pages by Using Column C and adding a "Start" and "End" word for where each page begins and ends,

I was giving this macro that then makes a list of the Start And End dates and set the Print Area to this and this work great up to about 15 pages but after that I just get "Unable to set the Print Area " in the debug
The macro looks like this>>>>

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Make_Pages_2()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  Dim c As Range
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]  Dim s As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  WithSheets("Inv PDF") 'this is the sheet with all the data on it.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    For Each c In.Range("C1", .Range("C" &.Rows.Count).End(xlUp)).SpecialCells(xlConstants)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      s = s &IIf(LCase(c.Value) = "end", ":J", ",D") &c.Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Next c[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   .Range("N1").Value = Mid(s, 2)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   .PageSetup.PrintArea = Mid(s, 2) '<- Option: As well as or instead ofthe previous line[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub

So what that gives me is This
"D3:J28,D30:J53,D55:J78,D80:J103,D105:J128,D130:J153,D155:J178,D180:J203,D205:J228,
D230:J253,D255:J279,D281:J304,D306:J329,D331:J354,D356:J379,D381:J404,D406:J429,
D431:J454,D456:J479,D481:J504,D506:J529,D531:J554,D556:J579,D581:J604,D606:J629,
D631:J654,D656:J679,D681:J704,D706:J729,D731:J754,D756:J779,D781:J804,D806:J829,
D831:J854,D856:J879,D881:J904,D906:J929,D931:J954,D956:J979,D981:J1004,D1006:J1029,
D1031:J1054,D1056:J1079,D1081:J1105,D1107:J1130,D1132:J1155,D1157:J1180,D1182:J1205,
D1207:J1230,D1232:J1255,D1257:J1280,D1282:J1305,D1307:J1330,D1332:J1355,D1357:J1380,
D1382:J1405,D1407:J1430,D1432:J1455,D1457:J1480,D1482:J1505,D1507:J1530,D1532:J1556,
D1558:J1581,D1583:J1606,D1608:J1631,D1633:J1656,D1658:J1681,D1683:J1706,D1708:J1731,
D1733:J1756,D1758:J1781,D1783:J1806,D1808:J1831,D1833:J1856,D1858:J1881,D1883:J1906,
D1908:J1931,D1933:J1956,D1958:J1981,D1983:J2006,D2008:J2031,D2033:J2056,D2058:J2081,
D2083:J2106,D2108:J2131,D2133:J2156,D2158:J2181,D2183:J2206,D2208:J2231,D2233:J2256,
D2258:J2281,D2283:J2306,D2308:J2331,D2333:J2356,D2358:J2381,D2383:J2406,D2408:J2431,
D2433:J2456,D2458:J2481,D2483:J2506,D2508:J2531,D2533:J2556,D2558:J2581,D2583:J2606,
D2608:J2631,D2633:J2656,D2658:J2681,D2683:J2706,D2708:J2731,D2733:J2756,D2758:J2781,
D2783:J2806,D2808:J2831,D2833:J2856,D2858:J2881,D2883:J2906,D2908:J2931,D2933:J2956,
D2958:J2981,D2983:J3006,D3008:J3031,D3033:J3056,D3058:J3081,D3083:J3106,D3108:J3131,
D3133:J3156,D3158:J3181,D3183:J3206,D3208:J3231,D3233:J3256,D3258:J3281,D3283:J3306,
D3308:J3331,D3333:J3356,D3358:J3381,D3383:J3406,D3408:J3431,D3433:J3456,D3458:J3481,
D3483:J3506,D3508:J3531,D3533:J3556,D3558:J3581,D3583:J3606,D3608:J3631,D3633:J3656,
D3658:J3681,D3683:J3706,D3708:J3731,D3733:J3756,D3758:J3781,D3783:J3806,D3808:J3832,
D3834:J3857,D3859:J3882,D3884:J3909,D3911:J3934,D3936:J3959,D3961:J3984,D3986:J4009,
D4011:J4034,D4036:J4059,D4061:J4084,D4086:J4109,D4111:J4134,D4136:J4159,D4161:J4184,
D4186:J4209,D4211:J4234,D4236:J4259,D4261:J4284,D4286:J4309,D4311:J4334,D4336:J4359,
D4361:J4384,D4386:J4409,D4411:J4434,D4436:J4459,D4461:J4484,D4486:J4509,D4511:J4534,
D4536:J4559,D4561:J4584,D4586:J4609,D4611:J4634,D4636:J4659,D4661:J4684,D4686:J4709,
D4711:J4734,D4736:J4759,D4761:J4784,D4786:J4809,D4811:J4834,D4836:J4859,D4861:J4884,
D4886:J4909,D4911:J4934,D4936:J4959,D4961:J4984,D4986:J5009,D5011:J5034,D5036:J5059,
D5061:J5084,D5086:J5109,D5111:J5134,D5136:J5159,D5161:J5184,D5186:J5209,D5211:J5234,
D5236:J5259,D5261:J5284,D5286:J5309,D5311:J5334" but as one long string of data I just broke it up to make it easier to read.

So the question is

How can I set up so my page breaks can be 200+ pages I'm happy for the code to be changed in any way it needs to be, please help if you can.

Tony
[/COLOR][/SIZE][/FONT]
<strike></strike>

 

[Fluff]

How about

Sub SetPages()
   Dim Cl As Range
   
   For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      If LCase(Cl.Value) = "end" Then
         ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Cl.Offset(1)
      End If
   Next Cl
End Sub

 

[tonywatsonhelp]

Thanks Fluff, this works great

 

[Fluff]

Glad to help & thanks for the feedback