Anbuselvam K
Member
Dear Excel Genius
The below code is exported as a pdf to the mentioned path with the specified range("B3:L37") and the name Q7.value as MMM-YYYY and O11.Value.pdf"
here the challenge is O11 cell has the dropdown validation list. Example values are "AAAA BBBBBB","CCCCC DDDDD EEEEEE","FFFFF GGGGGGGGG HH",....etc.,
I want to change the code to make the pdf depend on list values. If the validation list has 7 names then 7 pdf to be exported. if it is 100 then 100 pdf to be exported.
The file name and the path are the same. Please modify the code accordingly.
The below code is exported as a pdf to the mentioned path with the specified range("B3:L37") and the name Q7.value as MMM-YYYY and O11.Value.pdf"
here the challenge is O11 cell has the dropdown validation list. Example values are "AAAA BBBBBB","CCCCC DDDDD EEEEEE","FFFFF GGGGGGGGG HH",....etc.,
I want to change the code to make the pdf depend on list values. If the validation list has 7 names then 7 pdf to be exported. if it is 100 then 100 pdf to be exported.
The file name and the path are the same. Please modify the code accordingly.
Code:
Sub ExportAsPDF()
Dim folderPath As String
folderPath = ActiveWorkbook.Path & Application.PathSeparator & "Payslip/"
Dim yearMonth As String
Dim pdfFileName As String
pdfFileName = Format(ThisWorkbook.Worksheets("Payslip").Range("Q7").Value, "MMM-YYYY") & ThisWorkbook.Worksheets("Payslip").Range("O11").Value & ".pdf"
Dim pdfFilePath As String
pdfFilePath = folderPath & pdfFileName
If Dir(folderPath, vbDirectory) = "" Then
MsgBox "Folder not found: " & folderPath
Exit Sub
End If
If Dir(pdfFilePath) <> "" Then
Dim response As VbMsgBoxResult
response = MsgBox("File already exists. Do you want to overwrite it?", vbYesNo)
If response <> vbYes Then
Exit Sub
End If
End If
Dim exportRange As Range
On Error Resume Next
Set exportRange = ThisWorkbook.Worksheets("Payslip").Range("B3:L37")
On Error GoTo 0
If exportRange Is Nothing Then
MsgBox "Worksheet 'Payslip' or range 'B3:L37' not found"
Exit Sub
End If
exportRange.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=pdfFilePath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "PDF exported to " & pdfFilePath
End Sub