Macro to save worksheet as PDF

[CAPtain232]

Can someone help me write a macro to save the active worksheet as a PDF with filename referencing cell A1





Thank you

 

[sgmpatnaik]

@CAPtain

Hi

Please try the below code

Private Sub CommandButton2_Click()
'This macro opens the SaveAs option with the defult file path "you have to set this file path below" coverts the whole sheet into .pdf file format
'And opens the .pdf to view <-- you can disable the view after covert option with lower code: OpenAfterPublish:=False

    pdfName = ActiveSheet.Range("T1")
    ChDir "C:\Temp\" 'This is where youo set a defult file path.
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    End If
    MsgBox "File Saved to" & " " & fileSaveName
End Sub

Thanks

Patnaik

 

[Learner VBA]

Hello All
Thank you for providing such a great support to people like me.
I don't know VBA but understand basic and make changes in the back ground.

I have 2mb file of excel and want to make pdf of a sheet only. I am using following macro which is working fine as it is picking certain cells in file name.
My issue is it is saving in the same folder where file location is (which I guess coming from ".path") I need it to save in C:\XYZ folder but file name needs to be same as macro

Sub Save_ActSht_as_Pdf()
' Saves active sheet as PDF.
Dim Name As String
Name = ThisWorkbook.Path & "" & ActiveSheet.Name & Range("D11") & " Batch # " & Range("D15") & " " & Range("D34") & _
Format(Now(), " mm.dd.yy hh.mm") & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Please correct the code to put the file in C:\XYZ folder. I can ensure it will create the XYZ folder itself, if it can't find one.

Thanks in advance.

 

[Learner VBA]

I tried this macro but it gives me message to save as which I don't want to see. I was able to change the file name and location but can't delete the message box.

@CAPtain

Hi

Please try the below code

Private Sub CommandButton2_Click()
'This macro opens the SaveAs option with the defult file path "you have to set this file path below" coverts the whole sheet into .pdf file format
'And opens the .pdf to view <-- you can disable the view after covert option with lower code: OpenAfterPublish:=False

    pdfName = ActiveSheet.Range("T1")
    ChDir "C:\Temp\" 'This is where youo set a defult file path.
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    End If
    MsgBox "File Saved to" & " " & fileSaveName
End Sub

Thanks

Patnaik

 

[Learner VBA]

Thank you, I was able to do it.

Hello All
Thank you for providing such a great support to people like me.
I don't know VBA but understand basic and make changes in the back ground.

I have 2mb file of excel and want to make pdf of a sheet only. I am using following macro which is working fine as it is picking certain cells in file name.
My issue is it is saving in the same folder where file location is (which I guess coming from ".path") I need it to save in C:\XYZ folder but file name needs to be same as macro

Sub Save_ActSht_as_Pdf()
' Saves active sheet as PDF.
Dim Name As String
Name = ThisWorkbook.Path & "" & ActiveSheet.Name & Range("D11") & " Batch # " & Range("D15") & " " & Range("D34") & _
Format(Now(), " mm.dd.yy hh.mm") & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Please correct the code to put the file in C:\XYZ folder. I can ensure it will create the XYZ folder itself, if it can't find one.

Thanks in advance.

 

[Boorin]

Thank you, I was able to do it.

omg!! i have been loooking everywhere for this !!!! THANK YOU !!! and it worked!!

I have 2 extra question regarding the same code.

1) this code have a pop up to verify where we are saving. is there a way that it can automatically save to the destination mentioned without having that pop up?

2) Once the PDF is saved I need to sent it to another team as an attachment via outlook. Is there an added code that can generate outlook attaching the same file that was just saved or attach to a template on an outlook?

Sorry I know question 2 seems to be a bit too much, just trying to save time by not having to manually attach a file to each outlook. Also to avoid sending the wrong files to the wrong department.

Any help would be great !

 

[Learner VBA]

Look at my other post how to add 2nd email etc..., that code is perfect and no pop ups etc.

http://www.mrexcel.com/forum/excel-questions/970808-how-add-2nd-email-address.html

 

[hadham]

Hi,

I am using the following code to filter and print certain sheets in my excel. I need to change the printing of all specified sheets to be saved in one PDF file; please help. Thank you

Public Sub Filter_All()
Application.EnableCancelKey = xlDisabled
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
With sht
.Unprotect password:="helloFuture"
.Cells.EntireRow.Hidden = False
.Protect password:="helloFuture"
End With
Next
Call Sheet1_1
Call Sheet2_1
Call Sheet3_1
Call Sheet4_1

'Printing sheets in order
Dim SheetsArray As Variant
Dim i As Integer
SheetsArray = Array("Sheet5", " Sheet6", " Sheet8", " Sheet10", " Sheet11", " Sheet14", " Sheet15")
For i = 0 To UBound(SheetsArray)
Sheets(SheetsArray(i)).PrintOut Copies:=1
Next i
Application.ScreenUpdating = True
End Sub

 

[John_w]

I need to change the printing of all specified sheets to be saved in one PDF file

'Printing sheets in order
Dim SheetsArray As Variant
Dim i As Integer
SheetsArray = Array("Sheet5", " Sheet6", " Sheet8", " Sheet10", " Sheet11", " Sheet14", " Sheet15")
For i = 0 To UBound(SheetsArray)
Sheets(SheetsArray(i)).PrintOut Copies:=1
Next i
Application.ScreenUpdating = True
End Sub

Try this, with the sheet names exactly as written above - is there really a space before all the sheet names except Sheet5?

Public Sub Save_Multiple_Sheets_As_One_PDF()

    Dim currentSheet As Worksheet
    
    With ThisWorkbook
        Set currentSheet = .ActiveSheet
        .Worksheets(Array("Sheet5", " Sheet6", " Sheet8", " Sheet10", " Sheet11", " Sheet14", " Sheet15")).Select
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Multiple Sheets.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select
    End With
    
End Sub

 

[hadham]

Thank you John_w, it worked perfectly......
I have one more question; how do I include the workbook name within the saved file i.e. in the above example the saved file is under the name of "Multiple Sheets.pdf"; I need to include the workbook name before the Multiple....?
Thank you again...