zoom38
11-03-2015, 11:23 AM
Good afternoon all, I am looking to print a worksheet onto the front & back of 3 hole punched paper. When I print, in this case 5 pages, the back pages (2 & 4) are in the area of the hole punch. I found a sub on the internet that I thought would adjust the page setup for each page but it doesn't work. Does anyone know of a way to do this? Can it be done or am I out of luck?
Here is what i'm working with:
Sub PrintOddEven()
Dim hBreak As HPageBreak
Dim lNum As Long, lTotal As Long
Dim rLast As Range
With ActiveSheet
Set rLast = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
.PageSetup.PrintArea = .Range("A1", rLast).Address
ActiveWindow.View = xlPageBreakPreview
lTotal = .HPageBreaks.Count + 1
For Each hBreak In .HPageBreaks
lNum = lNum + 1
If lNum Mod 2 <> 0 Then
With .PageSetup
.RightMargin = 0
.LeftMargin = 30
.TopMargin = 30
.BottomMargin = 30
End With
'.PrintOut lNum, 1
Else
With .PageSetup
.RightMargin = 30
.LeftMargin = 0
.TopMargin = 30
.BottomMargin = 30
End With
'.PrintOut lNum, 1
End If
Next hBreak
End With
ActiveSheet.PrintPreview
ActiveWindow.View = xlNormalView
End Sub
Thanks
Gary
Here is what i'm working with:
Sub PrintOddEven()
Dim hBreak As HPageBreak
Dim lNum As Long, lTotal As Long
Dim rLast As Range
With ActiveSheet
Set rLast = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
.PageSetup.PrintArea = .Range("A1", rLast).Address
ActiveWindow.View = xlPageBreakPreview
lTotal = .HPageBreaks.Count + 1
For Each hBreak In .HPageBreaks
lNum = lNum + 1
If lNum Mod 2 <> 0 Then
With .PageSetup
.RightMargin = 0
.LeftMargin = 30
.TopMargin = 30
.BottomMargin = 30
End With
'.PrintOut lNum, 1
Else
With .PageSetup
.RightMargin = 30
.LeftMargin = 0
.TopMargin = 30
.BottomMargin = 30
End With
'.PrintOut lNum, 1
End If
Next hBreak
End With
ActiveSheet.PrintPreview
ActiveWindow.View = xlNormalView
End Sub
Thanks
Gary