Adjust page breaks according to table

[YasserKhalil]

Hello everyone
I have a code that generates excel files and at the end of the worksheet there is a table like that

Untitled.png

I am using this line to determine the print area and that's fine till now

Code: Select all

.PageSetup.PrintArea = "$A$1:$H$" & n + 6

But I have a problem as I don't want any row of the bottom table to be in a separate page.
For example, if you look at the snapshot, you will find that the page ends at row 179 so the bottom table will be in another page. In that case I need to include two of the names and the table in a separate sheet. I mean the last page should start at row 176 (to include two of the names plus the bottom table)

*if the bottom table appears completely with at least one name that is OK (I am talking about the last page only)

* The bottom table I mean from row 179 to row 184
The snapshot of the desired page break in that case would to be like that

Untitled.png

[HansV]

What is n and how do you calculate it?

[YasserKhalil]

The variable n is calculated as the last row in column C before adding the bottom table .. so n = lr + 2 which is in the example the row 179 (n+6 > to include the rows of the bottom table)

[HansV]

Your code sets the print area to "$A$1:$H$" & n + 6, but in your screenshot, the last used row is 184 = n + 7.

[YasserKhalil]

Yes but the last used row is 184 >> n+5 not n+6 ..
The problem in simple words, I don't want the bottom table to be isolated at any row and if this happens I would need to take two names and the bottom table to a separate page.

[YasserKhalil]

This is part of the code

Code: Select all

                With ActiveWorkbook
                    With .Worksheets(1)
                        .Shapes("Button 1").Delete
                        .UsedRange.Value = .UsedRange.Value
                        m = .Cells(Rows.Count, 3).End(xlUp).Row
                        n = m + 2
                        With .Range("E" & n)
                            .Value = "النتيجة"
                            .Offset(1).Value = "العدد"
                            .Offset(, 1).Value = "اجتاز"
                            .Offset(, 2).Value = "لم يجتاز"
                            .Offset(, 3).Value = "الجملة"
                            With .Resize(2, 4)
                                .Font.Size = 13
                                .Font.Bold = True
                                .Borders.Value = 1
                            End With
                        End With
                        With .Range("B" & n + 3)
                            .Value = "كتبه"
                            .Offset(1).Value = "أملاه"
                            .Offset(, 1).Value = "راجع الكتابة"
                            .Offset(1, 1).Value = "راجع الإملاء"
                            .Offset(, 1).HorizontalAlignment = xlLeft
                            .Offset(1, 1).HorizontalAlignment = xlLeft
                            .Offset(, 4).Value = "يعتمد ،،"
                            .Offset(1, 4).Value = "مدير المدرسة"
                            .Offset(2, 6).Value = "خاتم الشعار"
                            With .Resize(3, 7)
                                .Font.Size = 13
                                .Font.Bold = True
                            End With
                        End With
                        .PageSetup.PrintArea = "$A$1:$H$" & n + 6
                    End With
                    .SaveAs strPath & "\" & CStr(aL(i)) & "_" & sSchool, 51
                    .Close False
                End With

[HansV]

Oh sorry, I didn't read your previous reply carefully enough.

Try adding this:

Code: Select all

    Dim lp As Long
    lp = ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Location.Row
    If lp >= lr Then
        ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("A" & lr - 1)
    End If

Instead of ActiveSheet, you can use a variable of type Worksheet.

[HansV]

I see that your code uses m instead of lr, so replace lr with m in my previous reply.

[YasserKhalil]

Thanks a lot my tutor
I have edited the code like that (lr in my code is m variable)

Code: Select all

                        .PageSetup.PrintArea = "$A$1:$H$" & n + 5
                        lp = .HPageBreaks(.HPageBreaks.Count).Location.Row
                        If lp >= m Then
                            .HPageBreaks.Add Before:=.Range("A" & m - 1)
                        End If
                    End With

I got the same output. And when inspecting the variable lp I found the value of lp equals to 35 !!!
The value 35 is the first horizontal page break and not the last one I think.

[HansV]

Does this work better?

Code: Select all

    Dim hp As HPageBreak
    Dim lp As Long

    ...

                        For Each hp In .HPageBreaks
                            lp = Application.Max(hp.Location.Row, lp)
                        Next hp
                        If lp >= m Then
                            .HPageBreaks.Add Before:=.Range("A" & m - 1)
                        End If

[YasserKhalil]

That's weird. The same output and the lp variable equal to 35 after the loop.
When trying the part of loop away from the main code and I can get values but in the main code, I got only 35 as a value for the first page break only. That's weird in fact.

[HansV]

Could you attach a sample workbook?

[YasserKhalil]

Here's a sample of the first school ( I have about 55 schools)

[YasserKhalil]

The sample workbook is saved manually by me but in the main code it is created by the code (I think this is the problem why the page breaks are not recognized correctly) but I am not sure.

[YasserKhalil]

I also tried to move the lines of code that you have offered after the save line

Code: Select all

.SaveAs strPath & "\" & CStr(aL(i)) & "_" & sSchool, 51

but I still get the same output.
I also suspected the application screenupdating to be the cause of the problem and I have commented out the line of screenupdating and it is still the same problem. Page breaks are not recognized (just the first one)

[HansV]

You set the print area AFTER setting the page break. This resets the page breaks. If I move the line to set the print area up, it works for me:

Code: Select all

   ...

            .PageSetup.PrintArea = "$A$1:$H$" & n + 6
            For Each hp In .HPageBreaks
                lp = Application.Max(hp.Location.Row, lp)
            Next hp
            If lp >= m Then
                .HPageBreaks.Add Before:=.Range("A" & m - 1)
            End If
        End With

    ...

[YasserKhalil]

In my code I put this line ` .PageSetup.PrintArea = "$A$1:$H$" & n + 6` before the loop. And I also tried to put it after the loop (The same problem)

[HansV]

I'm afraid I can't help you. I have tested the code multiple times on your sample workbook, and it works correctly each time. Here is a screenshot of the last page of the print preview (my system doesn't support Arabic in VBA, so you'll see ??? in the cells populated by the code).

S0164.png

[YasserKhalil]

I think I have to attach the whole code with a sample of data to see the problem well. I have uploaded the sample file again to adjust the code.

[HansV]

Aaargh! It's a bug in Excel VBA.
You have to turn on screenupdating temporarily and select a cell below the data, otherwise HPageBreaks won't be correct.

Code: Select all

    ...
                        Application.ScreenUpdating = True
                        .Range("A" & n + 6).Select
                        .PageSetup.PrintArea = "$A$1:$H$" & n + 5
                        For Each hp In .HPageBreaks
                            lp = Application.Max(hp.Location.Row, lp)
                        Next hp
                        If lp >= m Then
                            .HPageBreaks.Add Before:=.Range("A" & m - 1)
                        End If
                        .Range("A1").Select
                        Application.ScreenUpdating = False
                    End With
                    .SaveAs strPath & "\" & CStr(aL(i)) & "_" & sSchool, 51
                    .Close False
                End With
    ...