Print Macro Not Working As Intended

boldcode · Aug 17, 2011

Hi Everyone,

For some reason I can't get my code to print the worksheet to FIT ALL COLUMNS ON PAGE. The macro currently cuts some of the columns.

Here is my code below:

Code:

Sub Print_PowerReport()
  Dim lastcol As Integer
  Worksheets("rptPower").PageSetup.PrintArea = Worksheets("rptPower").Range("A9").CurrentRegion.Address
  lastcol = Worksheets("rptPower").Cells(9, Worksheets("rptPower").Columns.Count).End(xlToLeft).Column
  On Error Resume Next  'continue if there are no PageBreaks
      With Worksheets("rptPower").PageSetup
          .DisplayPageBreaks = False
          .PrintTitleRows = "$9:$9"
          .LeftMargin = Application.InchesToPoints(0.5)
          .RightMargin = Application.InchesToPoints(0.5)
          .TopMargin = Application.InchesToPoints(0.75)
          .BottomMargin = Application.InchesToPoints(0.75)
          .HeaderMargin = Application.InchesToPoints(0.3)
          .FooterMargin = Application.InchesToPoints(0.3)
          .CenterHorizontally = False
          .Orientation = xlPortrait
          .FirstPageNumber = xlAutomatic
          .FitToPagesWide = 1
      End With
      Worksheets("rptPower").PrintPreview
      
      Dim Answer As String
      Dim MyQuestion As String
   
      MyQuestion = "Do you still want to print this report?"
   
      'Display MessageBox
      If MsgBox(MyQuestion, vbYesNo, "Print Question") = vbNo Then
          Sheets("TOC").Select
          Exit Sub
      End If
      
      'Ask User How Many Copies does he want to Print
      Dim iCopies As String
   
      iCopies = Application.InputBox("How many copies do you want to print?", Type:=2)
   
      If iCopies <> vbNullString Then
       
      Worksheets("rptPower").Columns("A:" & lastcol).EntireColumn.AutoFit
      Worksheets("rptPower").PrintOut Copies:=iCopies, Collate:=True
      Else
      Exit Sub
      End If
  End Sub

VoG · Aug 17, 2011

Try adding

Code:

.Zoom = False

boldcode · Aug 17, 2011

VoG,

I tried it, but all it does it shrink the worksheet in the print preview page really narrow and small. The "FIT TO COLUMNS ON ONE PAGE" under File, then Print, in Excel works the way I want the code to work. I just can't seem to get that option in the code.

VoG · Aug 17, 2011

You may need to add this as well (change 5 to suit)

Code:

.FitToPagesTall = 5

boldcode · Aug 17, 2011

VoG,

You nailed it!!! Thank you!!

ray:

Print Macro Not Working As Intended

boldcode

Active Member

Excel Facts

VoG

Legend

boldcode

Active Member

VoG

Legend

boldcode

Active Member

Similar threads

Forum statistics

Share this page

Print Macro Not Working As Intended

boldcode

Active Member

Excel Facts

VoG

Legend

boldcode

Active Member

VoG

Legend

boldcode

Active Member

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