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. Would you want to log a start now or wait until to

This answer was rated:

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

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 and 5 other Microsoft Office Specialists are ready to help you