Print a PDF using a String for the name

[KyleG]

I cant figure out how to get a sub to

'Here is what I have:

Sub ExportDeptOneKPI
CallKPIPrint ("Dept One")
End Sub

'This calls the below but how do I get the name in the spot with the ???????:

SubKPIPrint (PDFPrint as String)
Worksheets (PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\DEPARTMENTS\DATA\???????.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
End Sub

'the Export Sub then runs for Dept Two, Three etc.

Do I need to set the whole filename as a string before putting it into the export part of the code?
If you have an solution or alternate idea it would be much appreciated.

Also I would like this to all happen without actually calling up the sheets (they are all hidden).

thanks

 

[Michael M]

You mean like this

SubKPIPrint (PDFPrint as String)
Worksheets (PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\DEPARTMENTS\DATA\" & "Dept One" & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
End Sub

If you put your filename in say, cell A1, you could then change the filename in the cell to suit

SubKPIPrint (PDFPrint as String)
Worksheets (PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\DEPARTMENTS\DATA\" & range("A1").value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
End Sub

 

[DanteAmor]

Put the list of names of your leaves on a "Dept" sheet in the "A" column. eg.

Dept
<b>Dept</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:129.27px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">DEPT</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">Dept One</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">Dept Two</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">Dept Three</td></tr></table>


Use this code.
The sheets can be hidden, the macro must be visible momentarily to generate the pdf and then hide the sheet again.
Change "Dept" by the name of your sheet that contains the list of sheets to be sent to pdf

Sub ExportDeptOneKPI()
    Dim sh As Worksheet
    Set sh = Sheets("Dept") 'name of your sheet with the departments
    For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
        Call KPIPrint(sh.Cells(i, "A").Value)
    Next
End Sub


'This calls the below but how do I get the name in the spot with the ???????:
Sub KPIPrint(PDFPrint As String)
    On Error Resume Next
    Application.ScreenUpdating = False
    Sheets(PDFPrint).Visible = True
    Worksheets(PDFPrint).ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\trabajo\" & PDFPrint & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheets(PDFPrint).Visible = False
End Sub