HPageBreaks.Add

[tschop]

I am trying to manipulate the horizontal page breaks in an Excel 2007 spreadsheet through VBA. The line of code I am using is:

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

The code executes without error but does not move the horizontal page breaks. Any idea what I am doing wrong?

Thanks,

 

[Peter_SSs]

The code .. does not move the horizontal page breaks.

What page breaks should it be moving? You are not asking it to move any page breaks, just insert one every 44 rows.

 

[tschop]

Peter,

Thanks for the reply. I realized that yesterday after posting. I have now cleared all the existing breaks and then set new ones and it seems to work perfectly. Thanks for you quick response and help.

Tom

 

[Peter_SSs]

Glad you got it sorted.

 

[tschop]

The code that sort of works is:

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

Ideally, I want to put a break after every 44 rows. The above line of code seems to only put the code before the specified range. is there a way to make it add the break after the specified range?

Thanks,

Tom

 

[Peter_SSs]

The code that sort of works is:

Why not stick to the code that ..

... seems to work perfectly.