We help IT Professionals succeed at work.

VBA -> Printing trouble .. or rather page setup

calacuccia
calacuccia asked
on
Hi,

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

Application.Calculate

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 ...

calacuccia
Comment
Watch Question

cri

Commented:
Off the cuff, how about putting a short delay, be this a 1-2 sec timer or some input box asking for some confirmation ?

Commented:
Hi cri
Just a shot in the dark........ Have you tried a DoEvent in the loop after each time the page break is set ???
Peter

Author

Commented:
Excuse me for slow respons, Peter and cri, EE has been down a lot lately, and my app has been taking most of my time at work. I have not had the time yet to test your suggestions though, I will try today (I hope).

calacuccia
Geeze. I wonder if *I* have any open Qs like this. LOL

Could we get some feedback here?

We're cleaning up this topic area and need all the help we can get.

Thanks
amp
Community Support Moderator

:)
;)
:0

Author

Commented:
I still have not been able to see at the effects of the suggestions made here.
I feel so guilty :-)

No problem, Cal. I will not let you forget. ROFLMAO.
Reminder (you should put me in your system tray).

thanks!
amp
community support moderator
Hello everyone.

If no response to the comments below, I will delete this question and return the points to calacuccia.

We are cleaning up this topic area. This question is considered to be *outdated* and we would like you to move on with it.

Toward that end, we graciously request the following:

-----Askers-----

*Do NOT award points to moderators (me).
*Stay active in your question. Please respond to all experts' comments immediately.
*Do one of the following, please:

  --Award points to an expert for good answer or good effort.
  --Post that you'd like to withdraw the question (the DELETE button does NOT work).
  --Ask experts for more help.

Thanks very much for being fair to our experts.

-----Experts-----

*Do NOT provide further support to this question unless asked by the Asker.
*Post your opinion on who should receive points, even if it is you, or the question may be unnecessarily deleted.
*Don't thank the moderator for granting points to you, this only causes more email for everyone (but thanks for the thought!).

Finally, if anyone does not like the moderator's determination, say so immediately and suggest your own.

Your input is extremely valuable and very much appreciated.

thank you!
amp
community support moderator
amp@experts-exchange.com

Author

Commented:
amp, the vamp,

As this topic is completely moved off my priority list, and both suggestions here are valid approaches, for the least,

I propose a points split, 100/100 pts to forsborn and cri each.

If you halve the points here, I will do the rest.

Thanks
cal

Commented:
calacuccia,

Just on the off-chance that this is useful, this is an answer to a messy print area question that I had some time back.

Dave

Author

Commented:
DAve,

Good to see you, what answer do you mean?

calacuccia
Commented:
Hi cal,

You can either see the whole discussion: http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10308035

or, the bulk of the code was:


The following code will insert a subtotal at the bottom of each page, a blank row and a carried forward
subtotal on each subsequent page, and a grand total two cells below the final value in column B.  After
completing the print, it will also delete the inserted rows.  This code will run if your data is set
out as you previously provided i.e. Column A contains customer details, Column B contains Numeric data
for totalling.

If yoru sheet name is not Sheet1, then you will need to change those references in the code below.

It runs at around 3-4 seconds a page on my 400.  There are no doubt quicker ways to do this, however
this method is functional.

Sorry took a while to get back to you - have a lot on at present, and probably bit off more than I should
have.


Sub PrintSubTotals()

' Init looping variable
Counter = 1

Application.ScreenUpdating = False

' Refresh page breaks and clear print areas
ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.PrintArea = ""

' Loop for processing
While Worksheets("Sheet1").Cells(CStr(Counter + 1), 2).Value > 0


  ' Test if the current row + 1 has page break marker and next row in
  ' column B is not empty
  If Worksheets("Sheet1").Rows(Counter + 2).PageBreak <> xlNone And _
        Range("B" & Counter + 1).Value > 0 Then
     ' Select rows 1 either before and two after break, and insert three new rows
     Rows((Counter + 1) & ":" & (Counter + 3)).Select
     Selection.Insert Shift:=xlDown
   
     ' Show "Sub Total" in bottom of column A
     Cells(Counter + 1, 1).Select
     Cells(Counter + 1, 1).Value = "Sub Total"
     Selection.Font.Bold = True
   
     ' Set value to Sum This Page in column B
     Cells(Counter + 1, 2).Select
     ActiveCell.FormulaR1C1 = "=Sum(R[" & CStr(-Counter) & "]C2:R[-1]C2)"
   
     ' Format the SubTotal and CF cells
     Range("B" & Counter + 1).Select
     Selection.Font.Bold = True
        With Selection.Borders(xlEdgeTop)
           .LineStyle = xlContinuous
           .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
           .LineStyle = xlContinuous
           .Weight = xlThin
        End With
     
     Range("B" & Counter + 3).Select
     Selection.Font.Bold = True
     With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
     End With
     With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
     End With
   
     ' Show "Carried Forward" in Column A
     Cells(Counter + 3, 1).Select
     Cells(Counter + 3, 1).Value = "Carry Fwd."
     Selection.Font.Bold = True
   
     ' Set carry forward formula
     Cells(Counter + 3, 2).Select
     ActiveCell.FormulaR1C1 = "=(R[-2]C)"
   
     ' Refresh Page Breaks
     ActiveSheet.ResetAllPageBreaks
     ActiveSheet.PageSetup.PrintArea = ""
   
     ' Increment counter to skip the inserted blank row
     Counter = Counter + 1
   
     ' Keep track of position of last subtotal for Grand Total
     LastSub = Counter
   
  End If
 
  ' Inc counter for next in loop
  Counter = Counter + 1
Wend

' Select next empty cell in B, and insert Grand Total amount
Range("B" & Counter + 2).Select
ActiveCell.FormulaR1C1 = "=Sum(R[" & CStr(-(Counter - LastSub)) & _
                                                  "]C2:R[-1]C2)"
                                                 
' Format Grand Total cell
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
   With Selection.Borders(xlEdgeLeft)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
   With Selection.Borders(xlEdgeTop)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
   With Selection.Borders(xlEdgeBottom)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
   With Selection.Borders(xlEdgeRight)
       .LineStyle = xlContinuous
       .Weight = xlMedium
       .ColorIndex = xlAutomatic
   End With
 
' Provide text and format
Cells(Counter + 2, 1).Value = "Grand Total"
Range("A" & (Counter + 2) & ":B" & Counter + 2).Select
Selection.Font.Bold = True

'ActiveWindow.SelectedSheets.PrintOut Copies:=1



' Processing loop to remove inserted rows
For DeleteCounter = 1 To Counter

  If Range("A" & (DeleteCounter + 1)).Value = "Sub Total" Or _
     Range("A" & (DeleteCounter + 1)).Value = "Grand Total" Then
        Rows(DeleteCounter + 1 & ":" & DeleteCounter + 3).Select
        Selection.Delete Shift:=xlUp
  End If

Next

Application.ScreenUpdating = True

End Sub



There may be something of use - particularly if you can avoid relying on XL auto break points.


Also, while you are round, did you see this one:

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20299458


Dave
bruintjeFreelance
CERTIFIED EXPERT
Top Expert 2006

Commented:
what to do with this one? a triple split?
bruintjeFreelance
CERTIFIED EXPERT
Top Expert 2006

Commented:
Hello calacuccia

time to clean up
if not stated otherwise

my recom will be
-PAQ
-points split ,cri,forsbom,TigerMan
-this will be finalized with no further update (23.08.2002)

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
cri

Commented:
bruintje, thanks, but I hardly qualify for the split, it was a just a quick and dirty suggestion w/o any test.

Author

Commented:
Stating otherwise (I was back just in time :-)

All points go to Dave, although I did not test the method due to other priorities at work, it seems a good effort, which I will look at later, and I also give Dave all the points for the rude absence of any reaction from me.

calacuccia
bruintjeFreelance
CERTIFIED EXPERT
Top Expert 2006

Commented:
he he about time i know you would've liked to have the oldest open Q in this TA ;)

Commented:
Points from cal to dave?  What's the world coming to ....

Thx

NB: was of any use?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.