VBA to Print Multiple Print Areas from same sheet, both to 1 wide x 1 tall vs. scaling

DRMOE

New Member
Joined
Apr 10, 2019
Messages
5
Hello - I have a file with multiple common input sheets (same format for 15 tabs, all consolidate to a report if the tab is used/visible).

I need the following:

- Each visible sheet prints on two pages, using a manual break, set by a macro.

- Excel defaults to printing the documents via scaling rather than fitting each sheet to 1 wide x 1 tall.

Any ideas as to how i can get each print area to print to a single page versus scaling? The scaling causes the print out to come out wonky (super technical term...). Note - the sheet page breaks vertically in the VBA below.

Here's the vba I'm using - try not to laugh too hard:

Sub PRINTINPUTON2()
'
' PRINTINPUTON2 Macro
'

'
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$A$1:$AK$71"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$AK$71"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.37)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintSheetEnd
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.Papersize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 1
.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
Application.PrintCommunication = True
ActiveWindow.SmallScroll Down:=-81
ActiveSheet.ResetAllPageBreaks
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
ActiveWindow.View = xlNormalView
Range("C10").Select
End Sub
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Did you try changing the parameters here?
Code:
.FitToPagesWide = 2  'Change to 1 for single page.
.FitToPagesTall = 1
 

DRMOE

New Member
Joined
Apr 10, 2019
Messages
5
Thank you for the reply.

It does not solve the problem. Changing the Fit to 1x1 forces the two pages to combine into 1. I still need the two pages to print separately - I just want them both to scale properly.

As of now, the sheets do print out separately, which is fine - one side prints small due to scaling.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,721
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top