In a large application I am finalizing, I have trouble to get my page setup right.
The report, which is an Excel worksheet based on a template, is created on the fly at the end of my code.
When the report is finalized, I need to specify which pages need to be inside the 'Print Area', but I also want to adjust the Page Breaks.
Manual or forced page breaks are already partially preset in the template, but on some pages, the tables vary in length. I don't want a table to be split up in the middle of it, so I have used a method which finds me the start of the table and sets the page break by force on my preferred place.
The only problem is, that I have to rely on the automatic page breaks which Excel should fix after the report sheet is created and there lies my problem.
If I run the 'Manual Page Break' part of the macro, after the code has finalized, it works fine.
But, when inside the loop, it seems that Excel does not have the speed to calculate the page breaks, as they simply don't appear fast enough, which makes my code fail.
The question is, if anyone out here has experienced this type of behaviour, and obviously, if that particular person found a remedy for it.
I have tried to play with
Application.ScreenUpdating = False (or True)
Application.Calculation = xlCalculationManual 'or xlCalculationAutomatic
in all sort of ways, and by doing
prior to the page break part of the macro at no avail.
As an example, the line below is used to determine the total numbers of page to be printed, but does not work, for the same reason as explained above
qteSheet.PageSetup.RightFooter = "&P" & "/" & CStr(qteSheet.HPageBreaks.Count + 1)
May your thought be inspired ...