Create PDF macro button - PLEASE HELP

[tomwhi]

Hi Team,

Once again, I would really appricate your help!

Background:
I am creating a workbook - where the user selects difference criteria (slicers), which specifies which worksheet is relevent (out of 26 different options).

The user then inputs some varible which in turns updates the selected worksheet.

Problem:
Where I need help is the last stage. I want the user to be able to press a button and convert the specifed worksheet into a PDF (the worksheet name will be populate cell G10 of the 'Control Center' worksheet via a formula)

I hope I have provided enough information. Just ask if I need to clarify anything.

I am very much a novice when it comes to VBA...

THANK YOU FOR YOUR HELP!
Tom​

 

[JoeMo]

I'll assume you want to save the pdf file to the same folder that holds the workbook with the specified sheet, and that the pdf file name is the name of the specified sheet.

Sub CreatePDF()
Dim fPath As String, wsNam As String
fPath = ThisWorkbook.Path & Application.PathSeparator
wsNam = Worksheets("Control Center").Range("G10").Value
Worksheets(wsNam).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & _
    Worksheets(wsNam).Name & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[tomwhi]

I'll assume you want to save the pdf file to the same folder that holds the workbook with the specified sheet, and that the pdf file name is the name of the specified sheet.

Sub CreatePDF()
Dim fPath As String, wsNam As String
fPath = ThisWorkbook.Path & Application.PathSeparator
wsNam = Worksheets("Control Center").Range("G10").Value
Worksheets(wsNam).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & _
    Worksheets(wsNam).Name & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

PERFECT! use assumed correctly. Thank you

 

[JoeMo]

PERFECT! use assumed correctly. Thank you

You are welcome - thanks for the reply.

 

[tomwhi]

Hey Joe,

There could be a requirement for the PDF file name to include information from another cell.

for example. cell G10 is the worksheet name (Location Transfer), and cell C13 is the name of the letter recipient (John Smith). Therefore, could we get the file to be named "Location Transfer - John Smith"?

Thanks again for your help.

Tom

 

[tomwhi]

Hi,

believe I may have worked it out:

Sub CreatePDF()
Dim fPath As String, wsNam As String
fPath = ThisWorkbook.Path & Application.PathSeparator & Range("C13") & " "
wsNam = Worksheets("Control Center").Range("G10").Value
Worksheets(wsNam).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & _
    Worksheets(wsNam).Name & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub