Visual Basic to Print Current Page in Excel

[marycullen]

Excel 2003

Using Visual Basic I am creating a Project Tracking report using SubTotals. I am using the option "Page Break Between Groups" as the user wants be able to print details for a single Project at a time. Excel doesn't have a "Current" option in the print menu like Word so I don't know how to code to print just the page the user is current on.

I would appreciate any help.

 

[Cbrine]

I found this
http://www.ozgrid.com/forum/showthread.php?t=28860

Public Sub Print_Page_of_ActiveCell()
    Dim ActiveRow As Long, ActiveCol As Integer
    Dim iHPBs As Integer, iVPBs As Integer
    Dim iRow As Integer, iCol As Integer, iPage As Integer
     
    ActiveRow = ActiveCell.Row
    ActiveCol = ActiveCell.Column
    ActiveSheet.UsedRange
    If IsEmpty(ActiveCell.SpecialCells(xlCellTypeLastCell)) Then _
    ActiveCell.SpecialCells(xlCellTypeLastCell).FormulaR1C1 = " "
    If ActiveRow > ActiveCell.SpecialCells(xlCellTypeLastCell).Row Or _
    ActiveCol > ActiveCell.SpecialCells(xlCellTypeLastCell).Column Then _
    Exit Sub
     
    With ActiveSheet
        iHPBs = .HPageBreaks.Count
        iVPBs = .VPageBreaks.Count
        If iHPBs = 0 And iVPBs = 0 Then GoTo PrintSheet
Horizontal:
        For iRow = iHPBs To 1 Step -1
            If .HPageBreaks(iRow).Location.Row <= ActiveRow Then GoTo Vertical
        Next iRow
Vertical:
        For iCol = iVPBs To 1 Step -1
            If .VPageBreaks(iCol).Location.Column <= ActiveCol Then GoTo PrintSheet
        Next iCol
PrintSheet:
        iPage = (iRow + 1) + (iCol * (iHPBs + 1))
        .PrintOut From:=iPage, To:=iPage
        MsgBox "Printing page " & iPage
    End With
    If ActiveCell.SpecialCells(xlCellTypeLastCell).FormulaR1C1 = " " Then _
    Selection.SpecialCells(xlCellTypeLastCell).ClearContents
End Sub

HTH
Cal

 

[marycullen]

Cal, thank you very much, it works just fine.

Mary