I am having issues with the following code when trying to export a worksheet range to a PDF output
The issue is when either of the line to either SaveAs or Export as a PDF. I only need one in the code but have both in there to demonstrate what I have tried to use.
TIA
VBA Code:
Option Explicit
Sub CreateInvoices()
Dim wbInvoice As Workbook
Dim wsInvoice As Worksheet
Dim rngRow As Range
Dim strPath As String
Dim strFileName As String
Set rngRow = Sheets("Service Invoice").Range("SI_Start").Offset(1, 0)
Application.ScreenUpdating = False
Set wsInvoice = Sheets("Invoice")
strPath = ActiveWorkbook.Path & "/Invoices/"
Sheets("Invoice").Unprotect
Do Until rngRow.Offset(0, -17) = ""
If rngRow <> "" Then
If UCase(rngRow.Offset(0, 8)) = "Y" Then
Else
Range("Invoice_Offset") = rngRow.Row - Range("SI_Start").Row
strFileName = Range("Invoice_FileName")
strFileName = ReplaceIllegalChar(strFileName)
wsInvoice.Copy
Set wbInvoice = ActiveWorkbook
wbInvoice.SaveAs _
Filename:=strPath & strFileName & ".pdf", _
FileFormat:=xlPDF, _
PublishOption:=xlSheet
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPath & strFileName & ".pdf", _
Quality:=xlQualityMinimum, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Range("Invoice_Flatten").Select
Selection.Copy
Selection.PasteSpecial xlValues
Range("Invoice_Offset") = ""
Range("Invoice_FileName") = ""
Range("A1").Select
wbInvoice.SaveAs _
Filename:=strPath & strFileName & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
wbInvoice.Close savechanges:=False
Set wbInvoice = Nothing
rngRow.Offset(0, 8) = "Y"
End If
Else
End If
Set rngRow = rngRow.Offset(1, 0)
Loop
Range("Invoice_Offset") = ""
Sheets("Invoice").Protect
Set rngRow = Nothing
Set wsInvoice = Nothing
Sheets("Service Invoice").Select
Application.ScreenUpdating = True
MsgBox ("All invoices have been created"), vbInformation, "Invoices Created"
End Sub
TIA