VBA Convert to PDF - Drop Down List Stuck

[excel_learner27]

Hi,

I'm so close on this macro. I have a Dashboard sheet that dynamically updates based on a drop-down list in cell D4. I am attempting to create a macro that will auto-select each option in D4, refresh the data in the dashboard, and save as PDF. So far, this macro is doing it. Until it stops 1/4 of the way through my drop down list.

I've tried to troubleshoot the error code 1004, new destination folder, delete similar named files but it still stops at the same section of my drop down list.

My drop down list contains 70 items. What gives??

Sub DashboardToPDF()
Dim FolderName As String, fName As String
Dim inputRange As Range, r As Range, c As Range

Application.ScreenUpdating = False
'''' Open file dialog and choose folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = True Then
        FolderName = .SelectedItems(1) & "\"
    Else
        Exit Sub
    End If
End With

'''' Location of DataValidation cell
Set r = Worksheets("Dashboard").Range("D4")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)

'''' Loop through DataValidation list
For Each c In inputRange
    r.Value = c.Value
    fName = c.Value
    '''' Save as pdf
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next c
Application.ScreenUpdating = True
End Sub

Any feedback is greatly appreciated.

Best,
M

 

[Dan_W]

Usually, when there is some problem with code that involves file processing in a loop, it comes down to either an invalid filename, or the code is attempting to save a file to the folder where there already exists a file of the same name. That would explain why it keeps stopping at the same place in the list. You might want to check this by printing out the file names to the Immediate Window so you can work out which one is causing the problem.

I would suggest putting Debug.Print FolderName & fName after the line where you set fName's value, so it reads:

    r.Value = c.Value
    fName = c.Value
    Debug.Print FolderName & fName
    '''' Save as pdf

Out of curiosity, why is this line in there: r.Value = c.Value ?