Using VBA to Print Data From Excel to PDF

Question

Seemingly very simple question but I have an excel spreadsheet that uses vba code to take a screenshot of something, pastes it into a 'Screenshot' tab and then exports that tab to a pdf. My issue is that the page break that I pass does not line up with the dotted page break line that seems to be produced by default(?)...

Section of Code:

Set Screen = Sheets("Screenshots")
Set Block = Sheets("BlockChart")
Set CopyRangeBlock = Block.Range("A1:N51")
Set PasteRange = Screen.Cells(1, 1)
Application.DisplayStatusBar = True
CopyRangeBlock.CopyPicture xlScreen, xlPicture
DoEvents
Screen.Paste Destination:=PasteRange
DoEvents
Sheets("Screenshots").Rows(52).PageBreak = xlPageBreakManual
Application.CutCopyMode = False

The range of the data to screenshot is from "A1:N:51" and therefore I place a page break at row 52. However, a dotted page break line appears (seemingly by default) at row 50. This screws up my export to pdf and produces blank pages. This is particularly an issue when I loop through the code to generate multiple pages in a pdf. How can I make it so that the dotted line either doesn't appear or matches with the page break that I set so that I'm not getting extra blank pages?

Example:

Just to reiterate the point, the whole worksheet has pre-determined dotted lines for the print area. I essentially want to modify these (via manual breaks or something) so that each page printed to a pdf is a custom size that fits the data I screenshot.

Answer 1

By following code you can paste some example ranges as screenshots to the destination worksheet, each with a manual page break between them.

I left 1 row blank before and after each screenshot (reason: when a border of a shape is directly placed at a page break, the border might be printed on the adjacent page too).

Please adapt the zoom level in the last code line, to get even the largest screenshot printed on 1 page (e. g. 54 %). If you want to get it calculated automatically, see the second code part of this answer.

Private Sub CollectScreenshots()
    Dim wsSource As Worksheet, wsDestination As Worksheet
    Dim rngExampleRanges As Range
    Dim rngCopy As Range
    Dim rowPaste As Long
    Dim shpScreenshot As Shape

    Dim dlg As Dialog
        
    Application.DisplayStatusBar = True

    Set wsSource = Sheets("BlockChart")
    Set rngExampleRanges = wsSource.Range("A1:N51, A52:B53, C60:E99")
    
    Set wsDestination = Sheets("Screenshots")
    
    ' Copy all ranges as screenshot into destination worksheet:
    rowPaste = 1
    With wsDestination
        .ResetAllPageBreaks
        For Each rngCopy In rngExampleRanges.Areas
            rngCopy.CopyPicture Appearance:=xlScreen, Format:=xlPicture
            DoEvents
            
            If rowPaste > 1 Then .HPageBreaks.Add Before:=.Rows(rowPaste)
            .Paste Destination:=.Cells(rowPaste + 1, 1), Link:=False
            DoEvents
            
            Set shpScreenshot = .Shapes(.Shapes.Count)
            rowPaste = shpScreenshot.BottomRightCell.Row + 1
        Next rngCopy
    End With
    Application.CutCopyMode = False
    
    ' set appropriate zoom level
    wsDestination.PageSetup.Zoom = 54
    
End Sub

---

Automatic Zoom Level

If you want Excel to calculate the optimum zoom level, it is a bit more complicated.

If you have a cell range, e. g. A1:N51, which has to be printed on 1 page, then you can set the page dialog parameters manually like this:

• define the print area as A1:N51
• set scaling to 1 page width and 1 page height
• Then you can visually see the calculated zoom level within the page setup dialog.

Unfortunately you can not read this zoom level directly via VBA, as Worksheet.PageSetup.Zoom in this case returns False only. If you urge Excel to use the zoom level, e. g. by setting FitToPagesWide to False, Excel calculates a new zoom level.

To read the calculated zoom level, you have to send a keyboard shortcut to the page setup dialog. To get the correct keyboard shortcut for that, please check within your page setup dialog, which shortcut is used for zoom level. In my German Excel version, it's Alt + V.

Then exchange the last line from above code by following:

    ' get cell dimensions of the largest screenshot:
    Dim maxVerticalCells, maxHorizontalCells
    For Each shpScreenshot In wsDestination.Shapes
        maxVerticalCells = Application.WorksheetFunction.Max( _
            maxVerticalCells, _
            shpScreenshot.BottomRightCell.Row - shpScreenshot.TopLeftCell.Row + 1)
        maxHorizontalCells = Application.WorksheetFunction.Max( _
            maxHorizontalCells, _
            shpScreenshot.BottomRightCell.Column - shpScreenshot.TopLeftCell.Column + 1)
    Next shpScreenshot
    
    ' set appropriate zoom level
    With wsDestination
        
        ' Simulate a print area with required dimensions to get it printed to 1 page
        .PageSetup.Zoom = False
        .PageSetup.FitToPagesWide = 1
        .PageSetup.FitToPagesTall = 1
        .PageSetup.PrintArea = _
            .Range(.Cells(1, 1), .Cells(maxVerticalCells, maxHorizontalCells)).Address
    
        ' change the page setup to automatic and keep previous zoom level
        ' by sending keys to page setup dialog
        .Activate
        
        Dim strKeys As String
        strKeys = "P"               ' key "P" for first tab in that dialog
        strKeys = strKeys & "%V"    ' key <Alt>+<V> for automatic zoom (German, might be %A in other countries)
        strKeys = strKeys & "~"     ' key <Enter>
        SendKeys strKeys            ' send keys to following dialog
        Application.Dialogs(xlDialogPageSetup).Show
        Dim myZoomlevel As Double
        myZoomlevel = .PageSetup.Zoom
    
        ' Reset print area, reset automatic page adaption, use previous zoom level
        .PageSetup.PrintArea = ""
        .PageSetup.FitToPagesWide = False
        .PageSetup.FitToPagesTall = False
        .PageSetup.Zoom = myZoomlevel
    End With