Print current page with active cell with VBA code Excel 2010

**ruznemat** · 06-27-2013, 04:07 AM

Hi,

I have a workbook contains some worksheets, and every worksheet have multiple pages. I want to print only one page that contains active cell. Normally, i have to identify the workbook page then input the number twice as in “pages # to #” with the Print command. It is just waste of time.
I have fount the code as below. But it gives Error "Expected: line number or label or statement or end of statement" on this line "If .HPageBreaks(iRow).Location.Row <= activerow="" then="" goto="" vertical="" span="">"

If someone could help me to resolve the error problem, it'd be appreciated.

Many thanks in anticipation.

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="" span="">
    Next iRow
Vertical:
    For iCol = iVPBs To 1 Step -1
    If .VPageBreaks(iCol).Location.Column <= activecol="" then="" goto="" printsheet="" span="">
    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

**dangelor** · 05-12-2017, 08:42 AM

The original code:

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