Controlling Print with VBA

[Don Wells]

Running Office 2007 on Windows 7

I have a spreadsheet which I run daily that consists of a number of images grouped together. The size of the grouped image is always two pages wide and one or two pages high.

I have been trying (without success), to develop some code to print only the left-hand pages.

Any help will be greatly appreciated.

[Rudi]

Hi Don,

I found this code that will print every second page starting at the page you specify until the last page of the sheet.
If you specify Startpage as 1, it will print all ODD pages, conversely, 2 will print EVEN pages.

I don't know if this is exactly what you need, by try it out and see if it can be used?

Code: Select all

Sub PrintOddEven()
Dim TotalPages As Long
Dim StartPage As Long
Dim Page As Integer
    Application.PrintCommunication = False
    ActiveSheet.PageSetup.Order = xlOverThenDown
    Application.PrintCommunication = True
    StartPage = InputBox("Enter starting page number.", , 1)
    TotalPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    If StartPage > 0 And StartPage <= TotalPages Then
        For Page = StartPage To TotalPages Step 2
            ActiveSheet.PrintOut From:=Page, To:=Page, Copies:=1, Collate:=True
        Next
    End If
End Sub

[Rudi]

Please note that I added a Print order to the macro (the first three lines)...

[Don Wells]

Code: Select all

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim lr As Long
Dim pic As Shape
Set pic = ActiveSheet.Shapes(1)

  lr = pic.BottomRightCell.Row
  ActiveSheet.PageSetup.PrintArea = "$A$1:$J$" & lr
  
End Sub

Thanks for the further insight Rudi.