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
7043301
Type Your Microsoft Office Question Here...
Dr. Dan is online now
A new question is answered every 9 seconds

Hi Dan. Would you want to log a start now or wait until to

Resolved Question:

Hi Dan. Would you want to log a start now or wait until tomorrow
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Dr. Dan replied 1 year ago.

Dr. Dan :

I'm here

Dr. Dan :

let's see what we can do

Dr. Dan :

Do you have the Developer tab on your Main tab in Excel?

Dr. Dan :

  1. If the Developer tab is not available, do the following to display it:


    1. Click the File tab.


  1. Click Options, and then click Customize Ribbon.

  2. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.

Dr. Dan :

Once you have the Developer ribbon you should see a button called "Macros"

Dr. Dan :

See if you can identify if there is a print to PDF macro there

Dr. Dan :

If you see it, see if you can edit it and copy the code here

Dr. Dan :

Alternatively you could save a copy of your excel file with your data stripped and send me a copy of the file with the macros in place

Dr. Dan :

this way I can take a look at them and edit them accordingly

Customer:

I see the macros buton but not the print to macro. But I ususally just right click on the button and go in that way

Customer:

here is the macro code

Customer:

Sub SaveToPDFSingleRatesBond_2() PW = "Ph0en1x007" Set sh = Sheets("LINCS Costings - Single Bond") sh.Select sh.Unprotect Password:=PW Application.ScreenUpdating = False sh.Rows("2:14").Hidden = True sh.ResetAllPageBreaks u = sh.UsedRange.Row - 1 + sh.UsedRange.Rows.Count sh.PageSetup.PrintArea = "A1:Q142" With sh.PageSetup .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.35) .BottomMargin = Application.InchesToPoints(0.35) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FitToPagesWide = 1 End With ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A36") ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A89")


zPath = ActiveWorkbook.Path zFile = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "(Single Bond)" sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=zPath & "\" & zFile & ".pdf", Quality _ :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False


sh.Rows("1:14").Hidden = False Application.ScreenUpdating = True sh.Protect Password:=PW MsgBox "File saved as ' " & zFile & ".pdf '" & Chr(10) & "in the folder '" & zPath & "'" End Sub


Sub SaveToPDFSingleRatesNoBond_2() PW = "Ph0en1x007" Set sh = Sheets("LINCS Costings - Single No Bond") sh.Select sh.Unprotect Password:=PW Application.ScreenUpdating = False sh.Rows("2:14").Hidden = True sh.ResetAllPageBreaks u = sh.UsedRange.Row - 1 + sh.UsedRange.Rows.Count sh.PageSetup.PrintArea = "A1:Q142" With sh.PageSetup .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.35) .BottomMargin = Application.InchesToPoints(0.35) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FitToPagesWide = 1 End With ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A36") ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A89")


zPath = ActiveWorkbook.Path zFile = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "(Single No Bond)" sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=zPath & "\" & zFile & ".pdf", Quality _ :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False


sh.Rows("1:14").Hidden = False Application.ScreenUpdating = True sh.Protect Password:=PW MsgBox "File saved as ' " & zFile & ".pdf '" & Chr(10) & "in the folder '" & zPath & "'" End Sub


Sub SaveToPDFCoupleRatesBond_2() PW = "Ph0en1x007" Set sh = Sheets("LINCS Costings - Couple Bond") sh.Select sh.Unprotect Password:=PW Application.ScreenUpdating = False sh.Rows("2:14").Hidden = True sh.ResetAllPageBreaks u = sh.UsedRange.Row - 1 + sh.UsedRange.Rows.Count sh.PageSetup.PrintArea = "A1:Q142" With sh.PageSetup .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.35) .BottomMargin = Application.InchesToPoints(0.35) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FitToPagesWide = 1 End With ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A36") ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A89")


zPath = ActiveWorkbook.Path zFile = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "(Couple Bond)" sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=zPath & "\" & zFile & ".pdf", Quality _ :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False


sh.Rows("1:14").Hidden = False Application.ScreenUpdating = True sh.Protect Password:=PW MsgBox "File saved as ' " & zFile & ".pdf '" & Chr(10) & "in the folder '" & zPath & "'" End Sub


Sub SaveToPDFCoupleRatesNoBond_2() PW = "Ph0en1x007" Set sh = Sheets("LINCS Costings - Couple No Bond") sh.Select sh.Unprotect Password:=PW Application.ScreenUpdating = False sh.Rows("2:14").Hidden = True sh.ResetAllPageBreaks u = sh.UsedRange.Row - 1 + sh.UsedRange.Rows.Count sh.PageSetup.PrintArea = "A1:Q142" With sh.PageSetup .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.35) .BottomMargin = Application.InchesToPoints(0.35) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .PrintHeadings = False .PrintGridlines = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FitToPagesWide = 1 End With ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A36") ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A89")


zPath = ActiveWorkbook.Path zFile = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "(Couple No Bond)" sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=zPath & "\" & zFile & ".pdf", Quality _ :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=False


sh.Rows("1:14").Hidden = False Application.ScreenUpdating = True sh.Protect Password:=PW MsgBox "File saved as ' " & zFile & ".pdf '" & Chr(10) & "in the folder '" & zPath & "'" End Sub

Dr. Dan :

Great

Dr. Dan :

"A1:Q142"

Dr. Dan :

That should be your top left corner and bottom right corner

Dr. Dan :

Change Q142 to whatever your lower corner is now

Dr. Dan :

Save the macro and try it out

Dr. Dan :

There are a few references to A1:Q142

Dr. Dan :

Control H is a find and replace option

Dr. Dan :

it may work in the editor

Customer:

Sorry I should have been more clearer. I have three pages each with different print ranges.

Dr. Dan :

Then for the button to work, you will want to set the range for the largest (lowest right corner)

Dr. Dan :

Or make 3 variations of the button specific to each of the pages

Dr. Dan :

With the lower range for each of the macros

Dr. Dan :

respective for that page and that button

Customer:

Hmmm. I am not an IT person...and so wouldn't it be easier to just moive the table up one cell level

Dr. Dan :

Sure you could do that

Dr. Dan :

What else can I help you with this evening?

Dr. Dan :

I'm going to take a break right now, if you have another question that you would like addressed, please post it here and I will get to work on it as soon as I can, I will give you updates here

Customer:

If I move the table up one level wont the code have to be altered

Dr. Dan :

Code shouldn't need to be altered if the printable area is within the bounding box of A1 to Q142

Dr. Dan :

(those are the corners of your print region)

Dr. Dan :

A1 is the top left cell

Dr. Dan :

and Q142 is your bottom right corner

Dr. Dan :

that is your bounding box that the macro button is printing

Dr. Dan :

You can set your bounding box to a larger area by changing (if necessary) the Q142 limit

Dr. Dan :

or by moving your data into the rectangle created by A1 to Q142

Dr. Dan :

Either way would work

Dr. Dan :

If there is no data it will just generate blank output

Customer:

Ok...All good for now. I might be able to do the last bit myself. But may need you to help with some other functions. Will wait until I have rolled all of your codes across several other sheets and see how I go.

Customer:

Take care and thanks again

Customer:

Have a good night I will close off for now and be in touch again soon

Customer:

John

Customer:

Hi Dan are you online

Customer:

I need some more calculations done to the same project we worked on yesterday.

Customer:

Here goes. Cells D29, D30, D31, D32, D33, D34 and D35 are to be % of Cells C29, C30, C31, C32, C33, C34 and C35. The percentage amount is determined by what the percentage amount is inserted into Cells L35, O35, and R35. These cells - L35, O35, and R35 are to be included in the original table that the person selcts when chossing 'Y' at either L25, O25 or R35.

Customer:

I do not want a drop down list but please leave the cells (L35, O35, and R35) open for the person to insert the actual percentage amount.

Customer:

Thats 'Y' at either L25, O25 or R25

Dr. Dan :

Hi John

Dr. Dan :

Here is the new xlsx http://goo.gl/0MjdQ

Dr. Dan :

Caveats that the percentage field is assumed to be in percents (1-100) and not (0.01-1.0)

Dr. Dan :

Test this out to see if that does what you are requesting

Customer:

Hi Dan Yes all working fine. Thanks for your help. I am still developing the tool and will be trialling it soon to iron out any bugs so may need to call on you again. All the best John

Dr. Dan, Computer Software Engineer
Category: Microsoft Office
Satisfied Customers: 1073
Experience: B.S. Electrical & Computer Engineering, 20+ years technical experience
Dr. Dan and 3 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
 
 
 

Related Microsoft Office Questions