Link to home
Create AccountLog in
Avatar of 1r3o2n8
1r3o2n8Flag for United States of America

asked on

Printing

Attached file was designed with a lot of help from your solver cohorts.  Macro1, macro2 and macor3 do about the same thing except for the 1st column during printing.  A new problem has developed with the print prevew and then print code.  It just gets hung up and never prints.  You will see that the print sequence works ok thru print preview, then a msg box appears which asks "Print" or "Cancel".  Cancel works OK but print doesn't.  On the screen, the menu reappears but print never starts.  The plan was to display print preview so the report could be viewed for errors, then Print with normal page one with header, then print second and subsequent pages with no header and maximum lines per page.   Please help!.  Thanks in advance.
PSBWIRE-2010.xlsm
Avatar of dlmille
dlmille
Flag of United States of America image

That's because your code has hidden what you were going to print.  Don't hide the sheet until after printing.  I just tested:

Move the .visible = xlhidden after the call to PrintSpecial .  Do this for all your 3 macros and I believe you're problems will be solved!

Dave


     .PrintPreview
     
     xMsg = MsgBox("        Ready to Print?", vbOKCancel)
     If xMsg = vbOK Then Call PrintSpecial '/ changed print settings to print 1 page wide by 99 tall, which
                                           '/ creates pagebreaks you can use in PrintSpecial-EE 122510
     .Visible = xlHidden     '/  hides sheet "account number" after manipulation
    .Cells.Delete   'empties "account number" sheet-could have been one sheet for all 3 reports.
I just confirmed.  For some reason you had moved the .visible = .xlhidden up ahead of the call PrintSpecial.  Here's your corrected sheet:

May I suggest replacing all the lines in those 3 macros (from .PrintPreview thru to .Cells.Delete) and putting them in a separate macro?  That way, when you're organizing your logic, you ensure that this particular set of logic is always executed in a similar fashion.

E.g.,

sub PrintMyStuff()

     .PrintPreview
     
     xMsg = MsgBox("        Ready to Print?", vbOKCancel)
     If xMsg = vbOK Then Call PrintSpecial '/ changed print settings to print 1 page wide by 99 tall, which
                                           '/ creates pagebreaks you can use in PrintSpecial-EE 122510
     .Visible = xlHidden     '/  hides sheet "account number" after manipulation
    .Cells.Delete   'empties "account number" sheet-could have been one sheet for all 3 reports.

end Sub

PSBWIRE-2010-010211.xlsm
Avatar of 1r3o2n8

ASKER

Page 1 looks great.  Page 2+ didn't print with either your code or my modified code.  I tried only with macro1.
ok - will take a quick look
How do you want this to print - example - Tab "Account number" should print 2 pages, correct?  
Avatar of 1r3o2n8

ASKER

Each of the 3 rpts should print the same.  The number of pages for each report will depend on the number of rows occupied with data (records).  So the lst page shold be as printed with the header, footers and max number of lines of data.  Second and subsequent pages should have no header, but alll footers and max number of lines possible which is more than page 1 because the header is gone.  When all records have been printed, the last page will usually be less than full.  It doesn't make any difference what report is printing;  all records need to be printed in each report.
Ok - I made a few changes ahead of .printpreview in each of your macros

     .PageSetup.Zoom = 100
     .PageSetup.Zoom = False
     .PageSetup.FitToPagesWide = 1
     .PageSetup.FitToPagesTall = 99

so that there would be page breaks to count (recall, want to print the 2+ pages with different top margin in the PrintSpecial routine)

Then, I passed the worksheet to be printed to PrintSpecial() - e.g., PrintSpecial ("Benefactor") - so that PrintSpecial would print the correct tab!

Finally, I think the prior code may not have been printing the very last page.  Looking at the logic, I could not see how that would be the case, and it was printing a report with 2 pages that shed light on that.

So, I had the PrintSpecial code print the # pages = (number of hpagebreaks and vpagebreaks) * 2 (2 for each break)

To sum up, the .PrintOut lNum,1 really should have been more specific:

.Printout From:=lNum, To:=lNum (to print only one page at a time, and with defaults for .PrintOut for the rest of the parameters).

I hope this works properly for you, now!

Dave


PSBWIRE-2010-010211-r2.xlsm
Avatar of 1r3o2n8

ASKER

You have solved all problems but two.  The record count of 54 at the end of each page is correct.  However, not that many records actually printed.   I counted 52 on the beneficiary report and 51 on the other two.  Some records are getting lost.  The actual record count in "MAIN" and the printed record count are ok.  A few records are missing on the printed page.  Further research shows that all records show on print preview
but two or three disappear on the printed reports.

Problem 2 is slowness of macro1,2 & 3.  On this new computer with Windows 7, its takes 40-50 seconds to execute.  On the older computer with XP that I used during the holidays,  execution was almost immediate.
Is there a W7 option to speed this up or do yo have any other ideas?

I'm nearing the end of this project.  Thanks so much for your timely help!
where are the records that get lost?  at the top or bottom of a page, or in the middle.  Be specific.  Also, name 1 that is getting lost - specifically.  I can't help unless I can reproduce what you're seeing.

I have no ideas on speeding it up, with respect to operating system changes.

Dave
ok - I see the lost records... working...
What's happening is that the margins are changed after the 1st page - thus the page breaks change with the margin changes (e.g., Excel thinks those "missing records" are now on page 1, so no need to print them!

Let me think about this.

Dave
Avatar of 1r3o2n8

ASKER

My runs show top 3 records on page 2 did not print.  Originator names were KM, LKJL, & LKJL,  In my runs I added the row number to the originator name, so these two LKJL files were adjoining with suffixes 10 & 11 & I added 16 to KM.  None of these 3 printed.
Ok - believe all is printing correctly now.  I added dummy data to see how it prints with more than 2 pages.

Needed to get rid of the set pages to 1 wide, 99 tall.  I see you're using zoom to set the page right, so that's good.

In the PrintSpecial macro, I first determine where the first pagebreak is, then print the first page, then reset the print area from that first pagebreak to the end.  Then, make margin settings, get rid of header (because when print again, there'll be a first page because of print area reset), then print all pages from that point.

Let me know if this works!

Dave
PSBWIRE-2010-010211-r3.xlsm
Avatar of 1r3o2n8

ASKER

We're making great progress! The printing of records is now complete.  The right footer, page X out of X pages, needs attention since you start the printing over on P2+.  Thanks again.
Ok - take a close look at the With .PageSetup in the PrintSpecial() routine so you understand what I did.  I believe you set all the printing characteristics in each of your 3 macros before the .PrintPreview, so this should work OK.  I make changes during PrintSpecial which your macro then changes again, and then back to PrintSpecial, etc...

Works?

Dave
PSBWIRE-2010-010211-r4.xlsm
Avatar of 1r3o2n8

ASKER

Everything is working wonderfully.  Thanks you very much.  One more brief question or if you prefer, I can
ask a related question later:  the reports prints in two or more font sizes.  That is, one report will be one size and another report will be another.   I'd like a font size to get max lines ona  page and still readable.  I think we're using size 12 now.  Let's go for 10 and I can recode if necessary.  Notice that most headers and footers are size 12.
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 1r3o2n8

ASKER

I'll be asking another question.  Thanks again!  rgds/ron
Avatar of 1r3o2n8

ASKER

See related question in ID 34475960 above.