Greetings All - I have the below VBA which opens a Print Preview for each row on the "Agent Performance Scorecard" that has an "X" in column A. I'm needing to change it from Print Preview to opening in PDF instead. I've modified the code to open in PDF but it throws an error after opening the first form instead of opening the following rows which also have an "X". Any help would be greatly appreciated.
Code:
Sub PrintUsingDatabase()
'Option Base 0
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddr As Variant
Dim lOrders As Long
'http://www.contextures.com/xlForm03.html
Set FormWks = Sheets("Agent Form")
Set DataWks = Sheets("Agent Performance Scorecard-All")
myAddr = Array("C3", "C4", "C5", "C6", _
"D10", "E10", "D11", "E11", _
"D14", "E14", "D15", "E15", "D16", "E16", _
"D19", "E19", "D20", "E20", _
"H1", "H2", "H3", "H4", "H5", "H6", "H7", "H8", _
"E23")
With DataWks
'first row of data to
' last row of data in column B
Set myRng = .Range("B8", _
.Cells(.Rows.count, "B").End(xlUp))
End With
For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'if the row not marked, do nothing
Else
'clear mark for the next time
.Offset(0, -1).ClearContents
For iCtr = LBound(myAddr) _
To UBound(myAddr)
FormWks.Range(myAddr(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
'after testing, change Preview
' to False to Print
'FormWks.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
FormWks.PrintOut Preview:=True
lOrders = lOrders + 1
End If
End With
Next myCell
MsgBox lOrders & " forms were printed."
End Sub