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!
Bookmarks