Macro for Print to PDF via cell name.

Kaharthemad

New member
Joined
Feb 28, 2012
Messages
2
Reaction score
0
Points
0
Running into a problem here. I need a macro to save as a pdf using the cell A2 as part of the name, so if a2=Hospice of Atlanta the same file would be Hospice of Atlanta Survey.pdf.


Here is what I have so far. the code keeps failing somewhere and I am not sure why.
Code:
Sub Button279_Click()Dim Wb As Workbook
Sheets("Site Survey").Copy
Set Wb = ActiveWorkbook
s_dir = "C:\dump"
ActiveWorksheet.SaveAs Filename:="c:\dump" & Range("A2").Value
Wb.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=Mypath, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
Wb.Close False
ActiveWorkbook.Close False
MsgBox "Fact Sheet for " & Range("A2") & ".pdf saved in UHSFS2\Common\helpdesk\remote tech Surveys"


End Sub
any assistance would be most helpful
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
try changing the activeworksheet to activeworkbook.
Also it is generally a good idea to check if the file name already exists before saving to it and possibly overwriting data.
Ken has a good block of code to do this, I believe there is a link to it from the main page. or you can copy it here

Code:
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0
End Function


Code:
ActiveWorksheet.SaveAs Filename:="c:\dump" & Range("A2").Value
change to
Code:
ActiveWorkbook.SaveAs Filename:="c:\dump" & Range("A2").Value
 
Top