I am trying to print multiple pages on a single worksheet. Each page has the same number of row and a 65% scaling works fine in the interactive mode.
After I run the following macro, the scaling is always reset to 10%, which I know is the minumum... just not sure why the worksheet isn't being scaled to 75%?
any insight would be greatly appreciated.
Sub testPrintScale ()
ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Order = xlDownThenOver
.CenterHorizontally = True
.Zoom = 65
End With
' Remove vertical page breaks
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
For Each break In ActiveSheet.VPageBreaks
break.DragOff xlToRight, 1
Next break
firstLine = "my target string"
Set currentCell = ActiveSheet.Cells.Find(firstLine)
If Not currentCell Is Nothing Then
firstAddress = currentCell.Address
Do
count = count + 1
' Remove all Horizontal page break, so we can add our own
If count = 1 Then
If ActiveSheet.HPageBreaks.count > 0 Then
ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
End If
End If
currentCell.Select ' Set target cell as ActiveCell
ActiveCell.Offset(0, (-currentCell.Column) + 1).Activate ' Move ActiveCell column A of the current row
If count >= 2 Then
ActiveCell.Offset(-3, 0).Activate
'Set ActiveSheet.HPageBreaks(count - 1).Location = ActiveCell
ActiveCell.PageBreak = xlPageBreakManual
count = count + 1
currentCell.Select
'Set ActiveSheet.HPageBreaks(count - 1).Location = ActiveCell
ActiveCell.PageBreak = xlPageBreakManual
End If
Set currentCell = ActiveSheet.Cells.FindNext(currentCell)
Loop While Not currentCell Is Nothing And currentCell.Address <> firstAddress
End If ' End if currentCell
ActiveWindow.View = xlNormalView
Range("A1").Select
End Sub
Bookmarks