Save to PDF loop not working

Question

Solved.

The macro loops through a table and autofills values into the destination sheet and automatically saves as a pdf on the desktop with a specified file name for each row. It does not save them into a single pdf; however, if you have adobe acrobat it has a simple merge tool to combine them together.

  Sub AutoFill_export2pdf()
'

Dim rowCount As Integer
Dim CurBU As String
Dim CurOPRID As String
Dim CurName As String
Dim CurJournalID As String
Dim CurJournalDate As String
Dim FILE_NAME As String

 Sheets("List").Select

rowCount = ActiveSheet.UsedRange.Rows.count

Set Destsh = ActiveWorkbook.Sheets("Sheet")

For sourceRow = 2 To rowCount

CurOPRID = Range("A" & CStr(sourceRow)) 'OPRID
CurName = Range("B" & CStr(sourceRow)) 'Name
CurBU = Range("C" & CStr(sourceRow)) 'BU
CurJournalID = Range("D" & CStr(sourceRow)) 'Journal ID
CurJournalDate = Range("E" & CStr(sourceRow)) 'Journal Date

FILE_NAME = ActiveWorkbook.Path & "\" & "OTGL_" & "JRNL_" & CurBU & "_" &     CurJournalID & "_" & Format(CurJournalDate, "mm-dd-yyyy") & "_" & ".PDF"
CurName = "*" & CurName & "*"
CurBU = "*" & CurBU & "*"
CurJournalID = "*" & CurJournalID & "*"
CurJournalDate = "*" & CurJournalDate & "*"

Destsh.Range("K27") = CurName
Destsh.Range("D7") = CurBU
Destsh.Range("G7") = CurJournalID
Destsh.Range("I7") = CurJournalDate

On Error GoTo 0

Call SaveAsPDF(Destsh, FILE_NAME)

Sheets("List").Select

Next

End Sub


Public Sub SaveAsPDF(ByVal destSheet As Worksheet, ByVal PDFName As String)


On Error Resume Next
Kill PDFName

destSheet.Activate

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        PDFName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False,   OpenAfterPublish:=False

End Sub


Sub Autofill()
'

Dim rowCount As Integer
Dim CurBU As String
Dim CurName As String
Dim CurOPRID As String
Dim CurJournalID As String
Dim CurJournalDate As String
Dim FILE_NAME As String

CurName = "*" & CurName & "*"
CurBU = "*" & CurBU & "*"
CurJournalID = "*" & CurJournalID & "*"
CurJournalDate = "*" & CurJournalDate & "*"

Sheets("List").Select

rowCount = ActiveSheet.UsedRange.Rows.count

Set Destsh = ActiveWorkbook.Sheets("Sheet")

For sourceRow = 2 To rowCount

CurOPRID = Range("A" & CStr(sourceRow)) 'OPRID
CurName = Range("B" & CStr(sourceRow)) 'Name
CurBU = Range("C" & CStr(sourceRow)) 'BU
CurJournalID = Range("D" & CStr(sourceRow)) 'Journal ID
CurJournalDate = Range("E" & CStr(sourceRow)) 'Journal Date

FILE_NAME = ActiveWorkbook.Path & "\" & "OTGL_" & "JRNL_" & CurBU & "_" &    CurJournalID & "_" & Format(CurJournalDate, "mm-dd-yyyy") & "_" & ".PDF"

Destsh.Range("K27") = CurName
Destsh.Range("D7") = CurBU
Destsh.Range("G7") = CurJournalID
Destsh.Range("I7") = CurJournalDate

On Error GoTo 0

Call SaveAsPDF(Destsh, FILE_NAME)



Sheets("List").Select



Next


End Sub


End Sub

saves the entire workbook in pdf-which is great but I only need sheet 1 then you do Set wb = ActiveWorkbook | wb.ExportAsFixedFormat Type:=xlTypePDF XD — findwindow
Can you elaborate as to the difference between that line and mine...they appear the same to me. — TylerH
I wrote then you do meaning I am stating what you did... so there is no difference. — findwindow
My point is when you said you have tried about thirty ways to Sunday, I am doubting the veracity of your claim. My guess is you googled this code and didn't bother studying it at all. Edit: lol in before someone calls me rude again XD Yet, it's always OP who forces me to be blunt -_- Edit2: I guess I could just keep my mouth shut XD — findwindow
I have come across this error prior to the current script as well as different ones. I know what the code means and I have gone through the processes to eliminate the error. I am here for help...this is a forum-where individuals discuss ideas and resolve problems. If the subject has been touched before, there are many many different solutions making each problem unique-If you can find an answer for my problem within the forum please link it so I can move on. So yes, I have done my research and it has provided little benefit. No need to call people out when they are just looking for help. — TylerH

gtwebb gtwebb · Accepted Answer · 2016-06-02T16:53:19

You want to export just the Destination sheet (Destsh). So use

Destsh.ExportAsFixedFormat Type:=xlTypePDF, _
               filename:="fp", _
               Quality:=xlQualityStandard, _
               IncludeDocProperties:=True, _
               IgnorePrintAreas:=False, _
               OpenAfterPublish:=False

Instead of

wb.ExportAsFixedFormat Type:=xlTypePDF, _
               filename:="fp", _
               Quality:=xlQualityStandard, _
               IncludeDocProperties:=True, _
               IgnorePrintAreas:=False, _
               OpenAfterPublish:=False

Also this will just save the file to "fp" you want to use something like

filename:= fp & "\mysheetname.pdf"

Save to PDF loop not working

1 Answers