Print problem

[RJQMAN]

Hello - I thought I posted this question earlier today, but now I
cannot find it. My problem concerns formatting a page to print.

The page has multiple columns, and it is not possible to know how wide
the columns will be in advance - that is to say, each time the program
is used, the column widths are set automatically to agree with the
width of the data.

Then the result is printed. If there are more than a pre-defined
number of columns, the macro does a special format for page two, and
the printing appears on two pages for clarity

I would like to use the entire sheet of paper on each page so the
print is as large as practical. I wrote a macro to reformat the pages
for 2 sheets under certain conditions, and it works fine. However, I
cannot figure out how to use the 'fit to page' option. I would like
to have it in the macro so that the printout will expand as much as
practical to fill each of the two pages.

However, whenever I add the 'fit to page' statement to the program,
the page break disappears and does not function, 'even if I am
specifying print to 2 pages.' Here is part of the code;

ActiveSheet.PageSetup.PrintArea = "$D$2:$CC$246"
With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 1

Forgive me if I have double posted, but I do need some help.

 

[Zone]

If you tell Excel to fit to 2 pages wide, Excel will decide where to break
the pages. A possible workaround would be to set the print area for the
first page, print it using fit to page, then set the print area for the
second page and print it using fit to page. If this doesn't cause trouble
with your page numbering in the header/footer, then it might be workable.
Even if it does, you can also set the page number in your macro. James

 

[RJQMAN]

If you tell Excel to fit to 2 pages wide, Excel will decide where to break
the pages. A possible workaround would be to set the print area for the
first page, print it using fit to page, then set the print area for the
second page and print it using fit to page. If this doesn't cause trouble
with your page numbering in the header/footer, then it might be workable.
Even if it does, you can also set the page number in your macro. James












- Show quoted text -

Thanks for responding. My concern is that the users of the program
will not be very computer literate (even worse than me...) and will
probably go to the 'Print' command rather than knowing to trigger a
macro. Perhaps I have to do it by brute force, pre-calculating the
options and pre-setting the pages in their many configurations. That
I can do - but I was looking for a more elegant and less memory
intensive approach if there is one...

 

[Zone]

You can have your print setup occur automatically whenever a user prints.
Copy the code below. With the spreadsheet on the screen, right-click on the
Excel icon on the very left of the menubar and select View Code. Paste the
code in there. Adjust the print ranges as ActiveSheet.Name as needed. HTH,
James

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
With ActiveSheet.PageSetup
'set and print first page
.PrintArea = "a1:k20"
.RightFooter = "Page 1"
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 1
ActiveSheet.PrintOut
'set and print second page
.PrintArea = "L1:s20"
.RightFooter = "Page 2"
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 1
ActiveSheet.PrintOut
End With
End If
End Sub

 

[Zone]

Oops, typo. Last sentence should read "adjust the print ranges and
ActiveSheet.Name as needed"

 

[RJQMAN]

Oops, typo. Last sentence should read "adjust the print ranges and
ActiveSheet.Name as needed"








- Show quoted text -

I cannot seem to get this to work properly.

I put the code in the Workbook module. I could not get it to function
- multiple copies of the pages were printing, so I changed the code to
say "fit to pages wide - 1" and it now prints only one copy of the
first page, but 2 of the second page.

Plus I tried to introduce a variable so I could define when I wanted
to put all the data on a single page, but I couild not get that to
work in this arena other. Any way I can fix this??

 

[Zone]

I'm sorry you're having a problem with it. It seems to work fine for me, so
I can't recreate the problem. James