VBA Convert to PDF - Drop Down List Stuck

excel_learner27

New Member
Joined
Aug 6, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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??

VBA Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
709
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
    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 ?
 

Forum statistics

Threads
1,147,635
Messages
5,742,248
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top