excel_learner27
New Member
- Joined
- Aug 6, 2021
- Messages
- 1
- Office Version
- 2019
- Platform
- 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??
Any feedback is greatly appreciated.
Best,
M
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