Add a page break every 40th row in a range

[tonywatsonhelp]

Hi Everyone,

I need a macro to add horizontal page break every 40th row within Sheet "Data" range AD3:AJ851

So first page break is AD43.

also I need it to set it so its One page wide.

Any ideas how I can do this?

Thanks

Tony

 

[jim may]

Is this helpful?

https://www.pcmag.com/article2/0,2817,1778247,00.asp

 

[tonywatsonhelp]

Hi Thanks Jim but that was about doing it manually, I need a macro to do it automatically

 

[Fluff]

How about

Sub SetPageBreaks()
For i = 41 To Range("A" & Rows.Count).End(xlUp).row Step 40
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & i)
Next i
End Sub

 

[tonywatsonhelp]

Thanks Fluff,
This has done the job nicely
Tony

 

[Fluff]

Glad to help & thanks for the feedback

 

[tschop]

Fluff,

I tried to use that same code to manipulate my horizontal breaks, but it doesn't work. Any advice? The code I am running is below where a is the page I am on. So when a = 1, I want a break at row 44, when a = 2 at row 88 and so forth:

ThisWorkbook.Worksheets("Continuation Sheet " & Trim(Str(k + 1))).Activate
For a = 1 To sheetcount - 1
ThisWorkbook.Worksheets("Continuation Sheet " & Trim(Str(k + 1))).HPageBreaks.Add before:=Range("A" & (a * 44))
Next a

Any thoughts would be appreciated.

As a side note, I have also tried:


ThisWorkbook.Worksheets("Continuation Sheet " & Trim(Str(k + 1))).Range(sheetrange).rows(a * 44).EntireRow.PageBreak = xlPageBreakManual

Which did not work either.

Tom

 

[Fluff]

Have you tried the code I supplied in post#4?

 

[Peter_SSs]

Fluff,

I tried to use that same code to manipulate my horizontal breaks, but it doesn't work. Any advice? The code I am running is below where a is the page I am on. So when a = 1, I want a break at row 44, when a = 2 at row 88 and so forth:

ThisWorkbook.Worksheets("Continuation Sheet " & Trim(Str(k + 1))).Activate
For a = 1 To sheetcount - 1
ThisWorkbook.Worksheets("Continuation Sheet " & Trim(Str(k + 1))).HPageBreaks.Add before:=Range("A" & (a * 44))
Next a

Any thoughts would be appreciated.

As a side note, I have also tried:


ThisWorkbook.Worksheets("Continuation Sheet " & Trim(Str(k + 1))).Range(sheetrange).rows(a * 44).EntireRow.PageBreak = xlPageBreakManual

Which did not work either.

Tom

Duplicate to https://www.mrexcel.com/forum/excel-questions/1064117-hpagebreaks-add.html

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.