VBA to open form in PDF

DDT~123

Board Regular
Joined
May 31, 2012
Messages
220
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top