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: 1073
Experience:  B.S. Electrical & Computer Engineering, 20+ years technical experience
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

Resolved Question:

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
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Dr. Dan replied 1 year ago.

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, Computer Software Engineer
Category: Microsoft Office
Satisfied Customers: 1073
Experience: B.S. Electrical & Computer Engineering, 20+ years technical experience
Dr. Dan and 6 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional