View Single Post
 
Old 12-14-2020, 08:48 AM
Richystab Richystab is offline Windows 10 Office 2019
Novice
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default Current macro

Code:
Sub CombinedScienceExport()
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("Student Report Combined Science").Range("S2")
'''' 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:=False
Next c
Application.ScreenUpdating = True
End Sub

Last edited by macropod; 12-14-2020 at 02:53 PM. Reason: Added code tags
Reply With Quote