Create PDF of a worksheet based on selected criteria

[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 A9 of the 'Control Center' worksheet via a formula)

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

THANK YOU FOR YOUR HELP!
Tom

 

[njimack]

Doesn't sound too tricky. Does the below do the trick?

Dim Filename as String

Filename = Sheets("Control Center").Range("A9")

Sheets("Filename").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\"  & Filename, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[tomwhi]

Thanks Neil

I am receiving an error message on the second from last line of the code

Sub Button2_Click()
Dim Filename As String
Filename = Sheets("Control Center").Range("A9")
Sheets("Filename").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Filename, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub


Any thoughts?

 

[njimack]

What's the error message? What value has been assigned to the Filename Variable? Which version of Excel are you using?

 

[tomwhi]

Error message:
Run-time error '9'
Subscript out of range

File Name Varible:
Formula =IF('Varible Pivot Table'!$M$2=2,'Varible Pivot Table'!$B$2,"")
Current Value = Corp Offer Letter Dom Relo

Version:
Excel 2010

Thanks!

 

[njimack]

Do you definitely have a sheet by that name? Maybe we should test for the sheetname first...

Sub PDF()
Dim Filename As String
Filename = Sheets("Control Center").Range("A9")
If SheetExists(Filename) Then
    Sheets("Filename").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Filename, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
    MsgBox "There is no sheet named " & Filename, vbCritical, "Oops!"
End If
End Sub

Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

 

[tomwhi]

Thanks again Neil.

Yes 100% there is a worksheet with the extact same name.

Is your code two separate buttons?

 

[njimack]

Thanks again Neil.

Yes 100% there is a worksheet with the extact same name.

Is your code two separate buttons?

My code isn't any buttons - it's one procedure, which calls a function. That code works for me. I'd suggest recording a macro where you save the activesheet as a pdf. This will then give you the correct syntax, and you'll just need to replace any references to the activesheet with the Filename variable.

 

[tomwhi]

ok, thanks. I'll see if that works for me!

 

[tomwhi]

Hi -

I am still not able to get this to work.

Can anyone help?

Just to restate the problem:
I would like to create a macro button, where when is it pressed a PDF of a worksheet name in cell A9 will be created.

I sould also state my VBA skills are very close to nill

Thanks
Tom