Loop through data validation list and create PDF files

[Jasesair]

I have a drop down list in cell A3 of sheet "End of Season". When I select each item in the list, End of Season updates with the appropriate values. I am trying to loop through each item in the drop down list and then export a range as a pdf. The code does start to loop through and I can see the values changing to suit, but it finished with an error code: Run-time error '-2147024773 (8007007b): Document not saved.

I'm hoping I'm close. Any assistance to rectify would be much appreciated.

Sub PDFpack()

     Dim inputRange As Range
     Dim c As Range
     Dim strFilename As String
     
     Set inputRange = Evaluate(Range("d3").Validation.Formula1)
               
        For Each c In inputRange
        
            Range("d3").Value = c.Value
       
       With Sheets("Season Summary")
        .PageSetup.Orientation = xlLandscape
    End With
    strFilename = Sheets("Season Summary").Range("Q3") & " as at " & Format(Date, "d-mm-yyyy")
    Set inputRange = Sheets("Season Summary").Range("A1:v39")
    
        Next c
    inputRange.ExportAsFixedFormat Filename:=strFilename, Type:=xlTypePDF, OpenAfterPublish:=True


End Sub

 

[dkone]

I know you posted your question awhile ago and you probably got it fixed, but I was searching for an answer with looping and file names and saw your post. I think you need to set the full path not just the file name. They way you have it now, the VBA is telling Excel what to call the file, but now where to save it. I think this should get you close:

strFilename = "C:\" & Sheets("Season Summary").Range("Q3") & " as at " & Format(Date, "d-mm-yyyy")