Print to PDF and name file based on cell value.

[snuggles57]

I am trying to modify this code so it uses the value From Sheet("Minute") Cell Ref ("B2") as the file name. Any help appreciated.

Sub Printselection()

Dim rng As Range
Dim wks As Worksheet
Dim arr() As String
Dim i As Long: i = 0
For Each rng In Sheets("Minute").Range("E8:E11")
If Trim(rng.Value) <> "" Then
On Error Resume Next
Set wks = Nothing
Set wks = Sheets(rng.Value)
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Sheet " & rng.Value & " does not exist"
Else
ReDim Preserve arr(i)
arr(i) = wks.Name
i = i + 1
End If
End If
Next rng
Dim printSheets As Variant
printSheets = arr
Worksheets(printSheets).PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, PrToFileName:=PSFileName

 

[6StringJazzer]

I have not used PrintOut to send it to a PDF file. I would do this:

   Dim FileName As String
   FileName = Worksheets("Minute").Range("B2") & ".pdf"

   Worksheets(printSheets).ExportAsFixedFormat _
      Type:=xlTypePDF, _
      FileName:=FileName, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False

However, you might need to do this:

   Dim FileName As String
   FileName = Worksheets("Minute").Range("B2") & ".pdf"

   Worksheets(printSheets).Select
   ActiveSheet.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      FileName:=FileName, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False

 

[snuggles57]

I have a drop list in Cell E8:E11 that allows me to print different sheets based on what sheets are listed in the drop down lists would your code this still allow me to select the Sheets that I want to print. I need different sheets for different reports / emails.

For Each rng In Sheets("Minute").Range("E8:E11")

 

[snuggles57]

I modified to this. but it still is using the file name of the spreadsheet when I print to PDF. I need to keep this in the macro

Sub Printselection()

Dim rng As Range
Dim wks As Worksheet
Dim arr() As String
Dim FileName As String
FileName = Worksheets("Minute").Range("B2") & ".pdf"
Dim i As Long: i = 0
For Each rng In Sheets("Minute").Range("E8:E11")
If Trim(rng.Value) <> "" Then
On Error Resume Next
Set wks = Nothing
Set wks = Sheets(rng.Value)
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Sheet " & rng.Value & " does not exist"
Else
ReDim Preserve arr(i)
arr(i) = wks.Name
i = i + 1
End If
End If
Next rng
Dim printSheets As Variant
printSheets = arr
Worksheets(printSheets).PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, PrToFileName:=FileName 'PSFileName


End Sub

 

[6StringJazzer]

Your latest code doesn't use the code I provided so I don't what else I can do help.

 

[snuggles57]

Thank you for your help. I wasn't ignoring your previous suggestion just was not sure what I need to replace. I have amended the code as per your suggestion (I think) and it is now Printing with the required file name, but is saving into C:User\My documents. Previously it was saving in the same location as the spreadsheet file. I have a tried to add some code to refer to a path (MyPath) located in Worksheets("Lists").Range("M16") but I am not sure I have the code correct or even if the code is in the correct order. The path is located in the list worksheet as this would be a Template used by multiple people. Appreciate any assistance.

Sub Printselection()

Dim rng As Range
Dim wks As Worksheet
Dim arr() As String
Dim i As Long: i = 0
For Each rng In Sheets("Minute").Range("E8:E11")
If Trim(rng.Value) <> "" Then
On Error Resume Next
Set wks = Nothing
Set wks = Sheets(rng.Value)
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Sheet " & rng.Value & " does not exist"
Else
ReDim Preserve arr(i)
arr(i) = wks.Name
i = i + 1
End If
End If
Next rng
Dim printSheets As Variant
Dim MyPath As String
MyPath = Worksheets("Lists").Range("M16")
Dim FileName As String
FileName = Worksheets("Minute").Range("B2") & ".pdf"
printSheets = arr
Worksheets(printSheets).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=FileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

End Sub

 

[6StringJazzer]

Change this line

FileName = Worksheets("Minute").Range("B2") & ".pdf"

to this

FileName = ThisWorkbook.Path & "\" & Worksheets("Minute").Range("B2") & ".pdf"

You do not need MyPath.