Print Area-Fit to page

[Thomas Kuriakose]

Dear Sirs,

The following code checked on the net is setting the print area with last row and last column, kindly correct me if I am wrong.

but I am not able to fit to page with A3 page size and landscape orientation.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Range(Cells(Rows.Count, 1).End(xlUp)(2, 1), Cells(12, Columns.Count).End(xlToLeft)).Name = _
    "'" & ActiveSheet.Name & "'!Print_Area"
With ThisWorkbook.Worksheets("Sheet1").PageSetup
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
End Sub

Kindly help with the correct code.

thanks,

with regards,
thomas

 

[p45cal]

kindly correct me if I am wrong.

Range(Cells(Rows.Count, 1).End(xlUp)(2, 1), Cells(12, Columns.Count).End(xlToLeft)).Name = _
    "'" & ActiveSheet.Name & "'!Print_Area"

Should it be:

Range(Cells(Rows.Count, 1).End(xlUp)(2, 1), Cells(2, Columns.Count).End(xlToLeft)).Name = _
    "'" & ActiveSheet.Name & "'!Print_Area"

 

[Thomas Kuriakose]

Dear Sir,

Thank you very much for this correction. Kindly also let me know the correct code for fit to page 1.

Thank you so much,

with regards,
thomas

 

[p45cal]

Kindly also let me know the correct code for fit to page 1.

You've already got that part right.

 

[Thomas Kuriakose]

Dear Sir,

I checked the print preview and it is showing 7 pages in A3 (1-7)

Kindly recheck.

Thank you so much.

 

[p45cal]

Works here. Try the attached.

 

[Thomas Kuriakose]

Dear Sirs,

I tried and this is not working at my end.

Is my code correct, doe we need to add the page size in the code and do we need the fit to page option in the code.

kindly check and confirm.

thanks,

with regards,
thomas

 

[p45cal]

I don't know. Do you have some other code working somewhere?

 

[Thomas Kuriakose]

Dear Sirs,

I could not find anything more than these codes. I have tried again, but the print preview shows 1 of 7 pages.

The page setup required is -

Oiientation: Landscape
Size : A3
Fit to page as the number of columns keep varying with each project.

Thank you so much,

with regards,
thomaA

 

[Thomas Kuriakose]

@Luke M, Please check this code for the fit to page

thanks,

 

[Luke M]

Sounds like the part that's missing is forcing the landscape and A3 paper setup? If so, change the macro in ThisWorkbook module to this

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Range(Cells(Rows.Count, 1).End(xlUp)(2, 1), Cells(2, Columns.Count).End(xlToLeft)).Name = "'" & ActiveSheet.Name & "'!Print_Area"
With ThisWorkbook.Worksheets("Sheet1").PageSetup
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
    .Orientation = xlLandscape
    .PaperSize = xlPaperA3
End With
End Sub

 

[Thomas Kuriakose]

Dear Sir,

Thank you for the solution, but I am still getting 1 of 7 pages in the preview.

Kindly fin attached the workbook.

Thank you so much,

with regards,
thomas

 

[p45cal]

Try going ahead and actually printing, ignoring the print preview.

 

[Thomas Kuriakose]

Respected Sirs,

Thank you so much, now the code is working fine and am getting 1 page.

Thanks so much once again.

with regards,
thomas