How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Dr. Dan Your Own Question

Dr. Dan
Dr. Dan, Computer Software Engineer
Category: Microsoft Office
Satisfied Customers: 1081
Experience:  B.S. Electrical & Computer Engineering, 20+ years technical experience
7043301
Type Your Microsoft Office Question Here...
Dr. Dan is online now
A new question is answered every 9 seconds

Hi Dan I have a problem with my spreadsheet. Sheet 5 (LINCS

This answer was rated:

Hi Dan
I have a problem with my spreadsheet. Sheet 5 (LINCS Costings - Couple Bond) and Sheet 6 (LINCS Couple - No bond) will not show on three separate pages. For some reason it spreads the info over 10 to 12 pages. Sheets 3 and 4 work fine and the data is very similar. Can you help. I have a clean copy I can load up for you to view.
Thanks
John

Dr. Dan :

Hi John, sure think send me a link to the file and I'll take a look

Customer:

How do i do that

Dr. Dan :

You can go to http://www.ge.tt and upload it and then copy me the download link

Dr. Dan :

 


http://www.ge.tt


 

Customer:

Thanks here is the link to the file

Customer:

http://ge.tt/8vCb8rj/v/0

Dr. Dan :

Got it open

Dr. Dan :

What do you mean by 'will not show on three pages'

Dr. Dan :

when you convert to PDF?

Customer:

Yes it should only show the data on threee pages for example if you print to pdy you will see....by the way the password XXXXX XXXXX

Customer:

It only happens with the last two sheets

Dr. Dan :

let me take a look

Customer:

You can alos see it when you try to print normally

Customer:

The last two sheets should print to pdf the same as the 3 and 4th sheets

Dr. Dan :

I have all sheets saving to six pages of PDF's

Dr. Dan :

Take a look here: http://ge.tt/7bwKArj?c

Customer:

They should only save to three pages

Customer:

Are you using the save to pdf macro

Customer:

if you scroll straight down to the third page sterting from cell A1 there are 2 macro's. the second macro is a save to pdf. Try using that macro....it works in the 3 and 4th sheet but not in the last 2 sheets

Customer:

Dan...I have to go home now but will touch base agin in the morning. Have a good night. Take care

Dr. Dan :

looking into this

Dr. Dan :

I'll stepping through the macro

Dr. Dan :

I think the pagebreaks are reset by the macro

Dr. Dan :

and not getting set up in the vertical direction

Dr. Dan :

just horizontal

Customer:

I know that as the data gets to a certain size it can throw the pages out and I have to keep resizing with the font size and narrowing the cells to keep them o three pages

Dr. Dan :

Hmm

Dr. Dan :

Let's see if we can't get the PDF macro to resize

Customer:

Yes the macros do set the page sizes as I only need the tables on the left side of the sheet ... the ones that fit in the on page width if that makes sense

Customer:

that one page width

Customer:

the tables on the far right of the spreadsheet are for working out and are not to show in the final save to pdf

Customer:

If you go by the 2 and 3rd sheets as they are all good

Customer:

Sorry the 3rd and 4th sheets....I am getting tired :-)

Dr. Dan :

Debugged it

Dr. Dan :

The problem is that your fixed cell size is beyond the zoom scope that Excel can zoom down

Dr. Dan :

I have manually tried to reset the zoom and currently it is max zooming out to 10%

Customer:

Can you adjust for me

Dr. Dan :

which is trying to fit the page width

Dr. Dan :

Here is the issue

Dr. Dan :

If you go to Page Layout

Dr. Dan :

In Excel

Dr. Dan :

Note that there is a "Scale to Fit" area

Dr. Dan :

The spreadsheet is set to Width: Automatic

Dr. Dan :

Height: "Automatic"

Dr. Dan :

Scale:

Dr. Dan :

Set that to 10%

Dr. Dan :

that is the lowest that excel can go

Dr. Dan :

the Save to PDF macro doesn't care about your settings

Dr. Dan :

it will scale down to the smallest possible

Dr. Dan :

once you set Scale: 10% you will see the dotted pagebreaks

Dr. Dan :

you can see that the spreadsheet lops off a few cells to the right of the maximum pagebreaks

Dr. Dan :

this is what is being generated on another printed page (PDF) because it cannot fit it all to one horizontal sheet

Dr. Dan :

with Scale: 10% you will have a visual guide to resize or reposition your cells

Dr. Dan :

such that they fall within the maximum size

Dr. Dan :

Do you see what I am talking about John?

Dr. Dan :

Excel sets that limit to make sure that the print out is readable

Dr. Dan :

you may need to change your font size down

Dr. Dan :

The maximum page width is the rightmost border of the second section

Dr. Dan :

There is one workaround

Customer:

I see...I have reset the font size down and that seems to have fixed it.

Dr. Dan :

But it may not work for all your pages

Dr. Dan :

You can add this line to the PDF macro's

Dr. Dan :

ActiveSheet.UsedRange.Columns.AutoFit

Dr. Dan :

That will force all the columns to Autofit to the shortest width entry

Dr. Dan :

Much like you click on the divider line manually at the first row to do

Dr. Dan :

But if your cells have a lot of data it will still over run that sheet boundary

Dr. Dan :

and print out on another page

Customer:

I have another coding question for you...but am happy to pay for this question separately.

Customer:

I rest the font size down and that seems to have fixed the problem.

Dr. Dan :

using ActiveSheet.UsedRange.Columns.AutoFit I was able to resize everything to 3 pages, but you gave me empty cells so all the empty cells zero'ed out their width

Dr. Dan :

Sure it's getting a bit late, but if you want to accept this question, I can see if I can get the coding addressed tonight

Customer:

ok I will accept now and then ask another question

Dr. Dan and other Microsoft Office Specialists are ready to help you