Force range to fit to page

gint32 · Oct 12, 2018

Looking to find a way to force sheet contents regardless of range to fit to page and make font suit page size meaning if the sheet has only 5 rows of data then blow it up to full A4 size including making the font bigger to suit and if the data is lager than one page reduce …reasons being presently I have a workbook with about 10 sheets and I wish (via vba) to print them all off, and auto adjust via vba each sheet to the largest possible font and size for A4 landscape. Any help with this is appreciated

Thanks for reading

Ludwig · Oct 12, 2018

Just define the print area as required for each sheet, and "fit to 1 page wide by 1 page tall" - this is a one-time action not requiring VBA. Verify using Print Preview it looks okay. Should be no need to change font settings for any sheet as the "fit to page ..." will zoom/shrink for you as necessary.

You might only need a VBA macro to loop thru all sheetnames calling Sheets("Name").PrintOut with whatever parameters you require. Refer to https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.printout for options available.

Refer to https://support.microsoft.com/en-gb/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook to loop thru sheetnames to issue Printout commands.

gint32 · Oct 12, 2018

Ludwig said:
Should be no need to change font settings for any sheet as the "fit to page ..." will zoom/shrink for you as necessary.

Thanks and I have now utilised the vba for looping through,still though fit to page does not make the data any larger, as I said in my O.PIÂ’d like the sheet to fill the A4 regardless of the amount data not half fill as it does now, and yep IÂ’mstill needing the VBA for this,

So far the VBA I have which among other things sets landscape:

Code:

ActiveSheet.PageSetup.PrintArea = ("$A$1:$L$" & endrow)
    
    
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.15748031496063)
        .RightMargin = Application.InchesToPoints(0.15748031496063)
        .TopMargin = Application.InchesToPoints(0.551181102362205)
        .BottomMargin = Application.InchesToPoints(0.551181102362205)
        .HeaderMargin = Application.InchesToPoints(0.118110236220472)
        .FooterMargin = Application.InchesToPoints(0.118110236220472)
        .PrintHeadings = True
        .PrintGridlines = False
        .PrintComments = xlPrintSheetEnd
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With]

gint32 · Oct 13, 2018

Ludwig said:
Just define the print area as required for each sheet, and "fit to 1 page wide by 1 page tall" - this is a one-time action not requiring VBA. Verify using Print Preview it looks okay. Should be no need to change font settings for any sheet as the "fit to page ..." will zoom/shrink for you as necessary.

You might only need a VBA macro to loop thru all sheetnames calling Sheets("Name").PrintOut with whatever parameters you require. Refer to https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.printout for options available.

Refer to https://support.microsoft.com/en-gb/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook to loop thru sheetnames to issue Printout commands.

Can anyone help me with a part solution to this issue, or do you guys need more info or something? thanks guys and girls

gint32 · Oct 13, 2018

Ludwig said:
Just define the print area as required for each sheet, and "fit to 1 page wide by 1 page tall" - this is a one-time action not requiring VBA. Verify using Print Preview it looks okay. Should be no need to change font settings for any sheet as the "fit to page ..." will zoom/shrink for you as necessary.

You might only need a VBA macro to loop thru all sheetnames calling Sheets("Name").PrintOut with whatever parameters you require. Refer to https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.printout for options available.

Refer to https://support.microsoft.com/en-gb/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook to loop thru sheetnames to issue Printout commands.

Hey guys , so sorry to put this one on you guys

I didn't realize this was so tough a question for you's, I'll repost have a search and re-post on another forum soon as I find one that might be able to answer this one..thanks for all your help guys.. cheers

Force range to fit to page

gint32

Board Regular

Excel Facts

Ludwig

Board Regular

gint32

Board Regular

gint32

Board Regular

gint32

Board Regular

Similar threads

Forum statistics

Share this page

Force range to fit to page

gint32

Board Regular

Excel Facts

Ludwig

Board Regular

gint32

Board Regular

gint32

Board Regular

gint32

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock