Results 1 to 1 of 1

Remove Automatic Vertical Page Breaks from PDF VBA, Add to End

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-08-2008
    Location
    USA
    MS-Off Ver
    Mac Excel 2016
    Posts
    103

    Remove Automatic Vertical Page Breaks from PDF VBA, Add to End

    I have a VBA Excel macro that works almost perfectly, except vertical page breaks are appearing after the fifth row (though it's all relative to the width of the columns -- it vertically breaks later if I shrink the column widths) when I don't want them to start until the eight row. Can somebody help me tweak it?

    Essentially, the code currently creates new PDFs based on what's in Column I and then horizontally page breaks within each PDF based on what's in Column H. I just need it to stop vertically page-breaking in the middle of the data:

    Option Explicit
    Option Base 1
    Sub pdf()
    Dim ws As Worksheet
    Dim dArr() As String, outputPath As String, fileStem As String
    Dim dCol As Long, stRow As Long, endRow As Long, pStRow As Long
    Dim docCnt As Long, lnCnt As Long, c As Long, d As Long, gCol As Long
    Dim rwsPerPage As Integer, topM As Integer, botM As Integer
    Dim empNme As String, empGrp As String
    Dim rngRange As Range
    Dim i As Long
    
    Set ws = Sheets("Sheet1")
    dCol = 9    'col (pdf)
    gCol = 8  'col (group)
    stRow = 2   'row 2
    
    pStRow = stRow
    rwsPerPage = 21
    topM = 36   'default in points
    botM = 36   'default in points
    outputPath = "Macintosh HD:Users:Ryan:Desktop:"
    Set rngRange = Worksheets("Sheet1").Range("A2")
    fileStem = rngRange.Value
    
    docCnt = 1
    lnCnt = 0
    
    For i = 1 To Worksheets.Count
        Sheets(i).PageSetup.PrintTitleRows = "$1:$1"
    Next i
    
        With ws
            'set essential page parameters
            With .PageSetup
                .Orientation = xlLandscape
                .TopMargin = topM
                .BottomMargin = botM
            End With
            .ResetAllPageBreaks
    
            'last data row
            endRow = .Cells(Rows.Count, dCol).End(xlUp).Row
            'first employee pdf
            empNme = .Cells(stRow, dCol)
    
            'first group
            empGrp = .Cells(stRow, gCol).Value
    
                'for each data row
                For c = stRow To endRow
                    lnCnt = lnCnt + 1
                        'at change of employee pdf (col dCol)
                        If Not .Cells(c, dCol).Value = empNme Then
                            'put doc range into array
                            ReDim Preserve dArr(docCnt)
                            dArr(docCnt) = .Range(.Cells(pStRow, dCol - gCol), .Cells(c - 1, dCol - 1)).Address
                            docCnt = docCnt + 1
                            'reset startrow of new employee
                            pStRow = c
                            'reset empNme/empGrp
                            empNme = .Cells(c, dCol).Value
                            empGrp = .Cells(c, gCol)
                            'add hpage break
                            .HPageBreaks.Add before:=.Cells(c, dCol)
                            lnCnt = 0
                        Else
                            'at change of group (col gCol)
                            If Not .Cells(c, gCol).Value = empGrp Then
                                'reset empGrp
                                empGrp = .Cells(c, gCol)
                                'add hpage break (within pdf)
                                .HPageBreaks.Add before:=.Cells(c, gCol)
                                lnCnt = 0
                            End If
                        End If
    
                        'at page length
                        If lnCnt = rwsPerPage Then
                            'add hpage break
                            .HPageBreaks.Add before:=.Cells(lnCnt, dCol)
                            lnCnt = 0
                        End If
                Next c
    
                'last employee if appropriate to array
                If c - 1 > pStRow Then
                    ReDim Preserve dArr(docCnt)
                    dArr(docCnt) = .Range(.Cells(pStRow, dCol - gCol), .Cells(c - 1, dCol - 1)).Address
                End If
    
                'produce pdf files
                For d = 1 To UBound(dArr, 1)
                    .Range(dArr(d)).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                        outputPath & fileStem & d & ".pdf", Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                        OpenAfterPublish:=True
                Next d
    
        End With
    
    End Sub
    Thank you!
    Last edited by rylock; 11-13-2014 at 04:19 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Too Many Automatic Page Breaks
    By dheptig in forum Excel General
    Replies: 2
    Last Post: 03-10-2019, 11:59 AM
  2. Vertical Page Breaks
    By Matty5894 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-21-2014, 07:50 AM
  3. [SOLVED] How do I set the vertical page breaks between columns with a macro?
    By SL_Mills in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2012, 08:28 PM
  4. [SOLVED] How do I remove automatic page breaks?
    By Cici in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-23-2006, 05:00 PM
  5. Automatic Page Breaks
    By Ron in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-05-2006, 09:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1