0

I am trying to save an excel sheet as PDF on my desktop and use the delivery date as the file name for each pdf it saves, but I keep getting a Run-Time error. The error happens at "'Save the PDF file". Any help would be appreciated. Thank you

Sub ExportingPDF()

'Defining worksheets
Dim detailsSheet As Worksheet
Dim reportSheet As Worksheet

Set reportSheet = ActiveWorkbook.Sheets("Contract Form")
Set detailsSheet = ActiveWorkbook.Sheets("New POs")

'Looping the through each row
For i = 2 To 16

'Assigning values
SPile = detailsSheet.Cells(i, 2)
SClient = detailsSheet.Cells(i, 3)
SCommodity = detailsSheet.Cells(i, 4)
SOption = detailsSheet.Cells(i, 5)
SQtyMT = detailsSheet.Cells(i, 6)
SWhs = detailsSheet.Cells(i, 8)
SDeliveryCity = detailsSheet.Cells(i, 10)
SPO = detailsSheet.Cells(i, 12)
SDeliveryDate = detailsSheet.Cells(i, 15)
SWhsAddress = detailsSheet.Cells(i, 18)



'Generating the output
reportSheet.Cells(19, 1).Value = SPile
reportSheet.Cells(11, 1).Value = SClient
reportSheet.Cells(19, 3).Value = SCommodity
reportSheet.Cells(19, 2).Value = SOption
reportSheet.Cells(1, 1).Value = SWhs
reportSheet.Cells(12, 1).Value = SDeliveryCity
reportSheet.Cells(17, 1).Value = SPO
reportSheet.Cells(27, 3).Value = SDeliveryDate
reportSheet.Cells(5, 1).Value = SWhsAddress
reportSheet.Cells(22, 2).Value = SQtyMT

Error Happens here:
***'Save the PDF file
Worksheets("Contract Form").Range("A1:E28").ExportAsFixedFormat Type:=xlTypePDF, 
Filename:= _
"C:\Users\myname\Desktop\" & SDeliveryDate, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False***

With ActiveSheet.PageSetup
    .Zoom = False
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With

Next i

End Sub
Jonathan
  • 11
  • 5
  • 2
    Likely you need to `Format` the date so it doesn't contain illegal characters. Also you might consider concatenating the `".pdf"` extension. – BigBen Oct 28 '22 at 15:52
  • Thank you BigBen - it worked! Now I realized I have another question. I would like the macro to run until the last line in the detailsheet. Right now I have it run from row 2 to 16 but only have 5 rows in there today so I just got a run-time error even though the first 5 rows were saved perfectly. – Jonathan Oct 28 '22 at 16:02
  • [How to find the last used cell](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) – BigBen Oct 28 '22 at 16:03

0 Answers0