Right now I have a code that is working great (Which I got from this site) 
I need help to modify it to do something else.
I have a button that cycles through my validation list and creates PDF file for each value on the validation list.
My validation list is short for this post: Alameda county, Orange County
My tabs are named "User menu", "Cover letter", "report 1" and "report 2"
So right now, it creates a pdf called "Alameda County" that has these three sheets in the single pdf: Cover Letter, Report 1, and Report 2, then it automatically keeps going and creates another PDF named "Orange County" with the same sheets.
In the code, I specify what sheets to print to pdf. This is where I need to modify the code: instead of printing the same exact sheets each time, I want to always print one of the sheets called "Cover letter", but only print one other sheet either "report 1" or "Report 2" depending on the value of the validation list. For Alameda County, I want to print in a single pdf the "Cover Letter" and "Report 1". For "Orange County" I want to print "Cover Letter" and "report 2".
I am a novice, and I have listed the code below and I have highlighted in red where i think the change needs to take place. but I'm probably wrong....... If someone can graciously help me out I would really, really appreciate it:
Sub pdfall()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String
Dim r As Range
Set FSO = CreateObject("Scripting.FileSystemObject")
s(0) = ThisWorkbook.FullName
If FSO.FileExists(s(0)) Then
'//Change Excel Extension to PDF extension in FilePath
s(1) = FSO.GetExtensionName(s(0))
If s(1) <> "" Then
s(1) = "." & s(1)
sNewFilePath = Replace(s(0), s(1), ".pdf")
'//Prompt for folder destination
Application.FileDialog(msoFileDialogFolderPicker).Show
'//Export to PDF and go through validation list
For Each r In Sheets("User Menu").Range("grouplist")
Sheets("User menu").Cells(4, "C").Value = r.Value
Sheets(Array("Cover Letter", "Report 1", "Report 2")).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Sheets("User Menu").Range("C4").Value, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Sheets("User Menu").Select
Range("F16").Select
Next
End If
Else
'//Error: file path not found
MsgBox "Save this workbook first. Please save and try again. Important: Save as a Macro Enabled workbook"
End If
Set FSO = Nothing
End Sub
I need help to modify it to do something else.
I have a button that cycles through my validation list and creates PDF file for each value on the validation list.
My validation list is short for this post: Alameda county, Orange County
My tabs are named "User menu", "Cover letter", "report 1" and "report 2"
So right now, it creates a pdf called "Alameda County" that has these three sheets in the single pdf: Cover Letter, Report 1, and Report 2, then it automatically keeps going and creates another PDF named "Orange County" with the same sheets.
In the code, I specify what sheets to print to pdf. This is where I need to modify the code: instead of printing the same exact sheets each time, I want to always print one of the sheets called "Cover letter", but only print one other sheet either "report 1" or "Report 2" depending on the value of the validation list. For Alameda County, I want to print in a single pdf the "Cover Letter" and "Report 1". For "Orange County" I want to print "Cover Letter" and "report 2".
I am a novice, and I have listed the code below and I have highlighted in red where i think the change needs to take place. but I'm probably wrong....... If someone can graciously help me out I would really, really appreciate it:
Sub pdfall()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String
Dim r As Range
Set FSO = CreateObject("Scripting.FileSystemObject")
s(0) = ThisWorkbook.FullName
If FSO.FileExists(s(0)) Then
'//Change Excel Extension to PDF extension in FilePath
s(1) = FSO.GetExtensionName(s(0))
If s(1) <> "" Then
s(1) = "." & s(1)
sNewFilePath = Replace(s(0), s(1), ".pdf")
'//Prompt for folder destination
Application.FileDialog(msoFileDialogFolderPicker).Show
'//Export to PDF and go through validation list
For Each r In Sheets("User Menu").Range("grouplist")
Sheets("User menu").Cells(4, "C").Value = r.Value
Sheets(Array("Cover Letter", "Report 1", "Report 2")).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Sheets("User Menu").Range("C4").Value, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Sheets("User Menu").Select
Range("F16").Select
Next
End If
Else
'//Error: file path not found
MsgBox "Save this workbook first. Please save and try again. Important: Save as a Macro Enabled workbook"
End If
Set FSO = Nothing
End Sub
Any help would be greatly appreciated.
