locked
Macro to create Excel data to PDF file. RRS feed

  • Question

  • hi i have data in my spread sheet, assume Sheet1. I want user to view/save the data in PDF file automaticallu upon clicking command button. Viewing/saving any onething will work for me. Need complete steps please.

    Thanks,

    Robo.

    Wednesday, July 25, 2012 4:33 PM

Answers

  • Assuming that you have Excel 2007 or later:

    Press Alt+F11 to activate the Visual Basic Editor.

    Select Insert | Module.

    Copy the following code into the module window:

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\Export.pdf", _
            OpenAfterPublish:=False
    End Sub

    Change the path and filename as desired.

    Switch back to Excel.

    Activate the Developer tab of the ribbon.

    Click Insert > Button (Form Control)

    (This is in the Form Controls section of the Insert dropdown).

    Click on the worksheet.

    Excel will display the Assign Macro dialog.

    Select SavePDF in the list of macros, then click OK.

    You'll see a small button on the sheet.

    Right-click it to edit its text.

    Then click outside the button to activate it.

    If you want to edit it later on, for example to change its caption, position or size, right-click it.


    Regards, Hans Vogelaar

    • Marked as answer by Quist Zhang Friday, July 27, 2012 5:39 AM
    Wednesday, July 25, 2012 4:58 PM
  • This is Simply aswesome..i was googling all around for easy code they made me scared..

    Thanks Hans!

    Robo.

    • Marked as answer by Code_Robo Wednesday, July 25, 2012 5:45 PM
    Wednesday, July 25, 2012 5:45 PM

All replies

  • Assuming that you have Excel 2007 or later:

    Press Alt+F11 to activate the Visual Basic Editor.

    Select Insert | Module.

    Copy the following code into the module window:

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\Export.pdf", _
            OpenAfterPublish:=False
    End Sub

    Change the path and filename as desired.

    Switch back to Excel.

    Activate the Developer tab of the ribbon.

    Click Insert > Button (Form Control)

    (This is in the Form Controls section of the Insert dropdown).

    Click on the worksheet.

    Excel will display the Assign Macro dialog.

    Select SavePDF in the list of macros, then click OK.

    You'll see a small button on the sheet.

    Right-click it to edit its text.

    Then click outside the button to activate it.

    If you want to edit it later on, for example to change its caption, position or size, right-click it.


    Regards, Hans Vogelaar

    • Marked as answer by Quist Zhang Friday, July 27, 2012 5:39 AM
    Wednesday, July 25, 2012 4:58 PM
  • This is Simply aswesome..i was googling all around for easy code they made me scared..

    Thanks Hans!

    Robo.

    • Marked as answer by Code_Robo Wednesday, July 25, 2012 5:45 PM
    Wednesday, July 25, 2012 5:45 PM
  • Hans, this is good at my machine. But this is not working in another machine.. i had looked into other machine and found that Missing:Exprint 1.0 control library .. could not find ways to download it.

    Any other laternate approach please

    Thursday, July 26, 2012 5:50 PM
  • Activate the Visual Basic Editor.

    Select Tools | References...

    Clear the check box for eXPrint 1.0 Control Library.

    Click OK.

    Does this break the functionality of your workbook? If not, you should be good to go.


    Regards, Hans Vogelaar

    Thursday, July 26, 2012 6:12 PM
  • This formula works great and is saving PDF's after many many many hours of trying to find a solution

    I just need help with one thing, if I wanted to call that PDF as per the following, how would I do that?

    Quotation_344310_Company Name_Project Name

    The word quotation would always be there, but the other part are variable depending on the quote I am doing.  All of that data is held in one cell on my sheet (cell AY8)

    I hope you can help as I have been trying to do this now all weekend and I am losing the will to live.

    Sunday, September 8, 2013 7:13 PM
  • Try code like this. You'll have to replace C:\PDF with the path of the folder you want to save the worksheet in.

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\Quotation_" & _
            ActiveSheet.Range("AY8").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, September 8, 2013 7:54 PM
  • Hans, you are an absolute life saver.

    it works just as required

    I can not thank you enough!

    Sunday, September 8, 2013 7:56 PM
  • This works great, thanks.

    Question: Is there a way to automatically increment a number cell AY8 by 1 every time this macro button is clicked.

    Wednesday, November 20, 2013 4:28 PM
  • You can add a line to the macro:

        Range("AY8").Value = Range("AY8").Value + 1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 20, 2013 5:18 PM
  • That works great, you Rock!

    Thanks

    Wednesday, November 20, 2013 6:32 PM
  • Hi Hans,

    I want to apply conditional formatting (light red fill) to cell J14 based on two conditions. Firstly if there is any value (anything but blank) in B14  and secondly if there is no value (blank) in J14.

    Do you know how to do this.

    Thanks

    Thursday, November 21, 2013 4:26 PM
  • Select cell J14.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula   =OR(J14="",B14<>"")

    Click Format...

    Activate the Fill tab.

    Select light red.

    Click OK twice.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 21, 2013 4:56 PM
  • OR didn't work the way I wanted but AND did.

    =AND(J14="",B14<>"")

    Thanks again

    Thursday, November 21, 2013 10:14 PM
  • That wasn't quite clear from your description - "firstly if ..., and secondly if ..." suggested OR to me.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 21, 2013 10:42 PM
  • Hello Hans,

    Thanks, the code works exactly as you say.

    Can I ask for some additional help? Two points:

    1. How would I save the sheet with the active sheet name? I am working from one workbook with several sheets, all of which have to be saved and emailed.

    2. Is there a way to automate emailing the sheet in PDF, if I set an email address in a key cell on each sheet? Each sheet would be emailed to a different address.

    As an example, each sheet is named after a person. I need to send a pdf of each sheet to the individual it is named after.

    I hope you can help.

    Thanks,

    Iain 

    Thursday, December 19, 2013 1:38 AM
  • Ron de Bruin has a series of macros for creating and sending PDF files from Excel: http://www.rondebruin.nl/win/s5/pdf.htm

    The macro Mail_Every_Worksheet_With_Address_In_A1_PDF is the one you want.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 19, 2013 6:28 AM
  • Hello Hans,

    I can export PDF from Excel or PowerPoint with VBA.

    But My system need "PDF1.6" pdf files. Office export "PDF1.5".

    Manualy, I can export "PDF1.6"pdf file with Acrobat COM addin.

    Can I export pdf file with COM add-in, using VBA?

    Do you know how to do this.

    Thanks

    Thursday, January 16, 2014 7:40 AM
  • You can 'print' the sheet to the Acrobat printer:

    Sub Print2PDF()
        Dim strPrinter As String
        strPrinter = Application.ActivePrinter
        ' Use the full name of the Acrobat printer on your system
        Application.ActivePrinter = "Adobe Acrobat"
        ActiveSheet.PrintOut PrintToFile:=True, _
            PrToFileName:="..."
        Application.ActivePrinter = strPrinter
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, January 16, 2014 3:14 PM
  • Hello Hans,
    Thanks. The way was found.

    However, it has not completed owing to a setup of Acrobat.
    The contents of a file become .prn form only when VBA is used.
    I try succeedingly.
    Thanks.

    Friday, January 17, 2014 6:13 AM
  • Hi Hans

    Thanks for the solution, it works perfectly. Was just wondering, instead of hardcoding the save location in the code, can we prompt the user to provide a location (and thus the filename) of his/her choice?

    Thanks

    Roy

    Wednesday, March 12, 2014 12:10 AM
  • You could use code like this:

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 12, 2014 7:03 AM
  • Hans

    I hope you can help. I am trying to add a button for PDF as described above but am getting an error message.

    Run time error 1004

    Can you please help

    Wednesday, March 19, 2014 4:17 PM
  • Which version of Excel are you using?

    Which line is highlighted if you click Debug in the error message dialog?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Wednesday, March 19, 2014 5:35 PM
  • Hans, I am using this formula but have questions.

    ONE
    I have two sheets in my workbook. I want my command button on Sheet1 and I want it to export data from a range on Sheet2. The Range is A49:AZ49 (6 pages).

    TWO
    I am using this is a proposal tool for my customers too. If I have more than one proposal for the same customer, it will export the same pdf name. Is there a way to have it automatically number the proposals? Example: johnson1  My pdf is also being named after their name which is on Sheet 1 in cell C2.

    Saturday, April 5, 2014 8:26 PM
  • Try this version:

    Sub SavePDF()
        ' Modify path as needed but keep the trailing backslash
        Const strPath = "C:\PDF\"
        Dim strFile As String
        Dim n As Long
        strFile = Range("C2").Value
        Do
            n = n + 1
        Loop Until Dir(strPath & strFile & n & ".pdf") = ""
        Worksheets("Sheet2").Range("A49:AZ49").ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strPath & strFile & n & ".pdf", _
            OpenAfterPublish:=False
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 5, 2014 10:21 PM
  • Hans

    Instead of exporting directly how do I get the code to present save as box instead so I can then choose file name and location. Could I set a default location when save as box opens?

    Thanks

    Jim

    Saturday, May 31, 2014 8:39 PM
  • For example:

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="\\server\share\folder\", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub

    You can change the path in InitialFileName to suit your needs.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, June 14, 2014 12:07 PM
  • Hi Hans,

    Need your esteemed help.

    1) Is it possible to add a mailing (outlook) option to this created pdf files to the email addresses from a colomn (eg: C), after validating another colomn (eg: A) with file names, in a sheet (eg: Sheet6) within the workbook?

    2) Will the output of - ActiveSheet.Range("AY8").Value & ".pdf", _
    be "Alfa.pdf" if value of cell AY8 is Alfa?

    Thanks,

    Raj

    Wednesday, June 18, 2014 7:23 AM
  • 1) I don't understand your question completely. Could you explain it in more details?

    2) Yes, that is correct.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 18, 2014 4:19 PM
  • Hi Hans,

    Thanks for the simple explanation above. I may have missed it in your previous threads, so apologies if you've already answered this question! 

    I have managed to export the PDF using the code above, however is there a way to select the directory you wish to save the file into manually? In the above example, the file is saved in the directory specified in Filename:= 

    So I'd like to be able to click PDF button, then for it to ask me where I would like to save the PDF whilst also allowing me to rename the file.

    Thanks!

    Saturday, July 19, 2014 9:24 AM
  • The code in my reply from June 14, 2014 shows how to do that:

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="\\server\share\folder\", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub

    The macro will show the Save As dialog. InitialFileName specifies the default path; you can change that or you can omit the line

           InitialFileName:="\\server\share\folder\", _

    entirely


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, July 19, 2014 10:33 AM
  • Hans -

    You're answers on this topic have been very helpful, thank you. I have one question.

    I'm using the below code which is perfect for me, but I want to add one thing. I want to be able to select the previous sheet so that it is printed as well. I want the previous sheet to be the first page that gets printed in the PDF, so the current sheet (The sheet with the "Print PDF" button) gets printed as the second sheet.

    Your help in solving this would be appreciated it.


    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="\\server\share\folder\", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub

    Tuesday, October 7, 2014 11:17 PM
  • You can use this version:

    Sub SavePDF()
        Dim strFileName As String
        Dim wsh As Object
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="\\server\share\folder\", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            Set wsh = ActiveSheet
            Worksheets(Array(wsh.Name, wsh.Previous.Name)).Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
            wsh.Select
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by MikeBaca Wednesday, October 8, 2014 11:25 PM
    Wednesday, October 8, 2014 6:06 AM
  • Works perfect, thanks Hans!
    Wednesday, October 8, 2014 10:31 PM
  • This formula helped me very much.

    Would it be possible to conditionally select more than one sheet and convert them into one single PDF file?

    Wednesday, October 22, 2014 10:37 PM
  • Please provide more detailed information.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 23, 2014 6:05 AM
  • I am using this formula and it has helped me very much. Now I just need a formula where I can conditionally select sheets. Now all I need is that instead of automatically selecting both pages (like in the formula below) there could be a condition somewhere, for example: To have a number in A1 so when I put a 1 there, it will save the first sheet, 2 the second sheet, and also an option in which I can save several selected sheets at the same time.

    And also would it be possible to put a code that saves the file automatically?

    I very  much appreciate all the help

    Sub SaveMULTIPLEPDF()
    
    Sheets(Array("Sheet1", "Sheet2")).Select
    Dim saveLocation As String
    saveLocation = Application.GetSaveAsFilename( _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If saveLocation <> "False" Then
    ActiveSheet.ExportAsFixedFormat xlTypePDF, saveLocation, xlQualityStandard
    End If

    Thursday, October 23, 2014 2:35 PM
  • Like this one but instead of printing, it saves it in a PDF file automatically.
    Sub PrintStuff()
        Dim vShts As Variant
    
        vShts = Sheets(1).Range("A1")
        If Not IsNumeric(vShts) Then
            Exit Sub
        Else
            Select Case vShts
                Case 1
                    Sheets("Sheet1").PrintOut
                Case 2
                    Sheets("Sheet2").PrintOut
                Case 3
                    Sheets("Sheet1").PrintOut
                    Sheets("Sheet2").PrintOut
            End Select
        End If
    End Sub

    Thursday, October 23, 2014 2:50 PM
  • For example:

    Sub PrintStuff()
        Dim vShts As Variant
        Dim strFileName As String
    
        vShts = Sheets(1).Range("A1")
        If Not IsNumeric(vShts) Then
            Exit Sub
        Else
            strFileName = Application.GetSaveAsFilename( _
                InitialFileName:="\\server\share\folder\", _
                FileFilter:="PDF Files (*.pdf),*.pdf", _
                Title:="Save As PDF")
            If strFileName <> "False" Then
                Select Case vShts
                    Case 1
                        Sheets("Sheet1").Select
                    Case 2
                        Sheets("Sheet2").Select
                    Case 3
                        Sheets(Array("Sheet1", "Sheet2")).Select
                End Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=strFileName, _
                    OpenAfterPublish:=False
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 23, 2014 3:16 PM
  • Thank you very much!!! You are very awesome

    And I just need one more thing.

    That instead of selecting every time where to save it, to put a predetermined folder in which I could save automatically too.

    Thursday, October 23, 2014 4:11 PM
  • Would you like a fixed filename?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 23, 2014 6:04 PM
  • Yes, that's the only thing I need now.

    Thank you.

    Thursday, October 23, 2014 7:54 PM
  • You can simply replace the call to Application.GetSaveAsFilename with a fixed path:

    Sub PrintStuff()
        Dim vShts As Variant
        Dim strFileName As String
    
        vShts = Sheets(1).Range("A1")
        If Not IsNumeric(vShts) Then
            Exit Sub
        Else
            ' Change path and filename as desired
            strFileName = "C:\MyFolder\MySubfolder\MyFile.pdf"
            If strFileName <> "False" Then
                Select Case vShts
                    Case 1
                        Sheets("Sheet1").Select
                    Case 2
                        Sheets("Sheet2").Select
                    Case 3
                        Sheets(Array("Sheet1", "Sheet2")).Select
                End Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=strFileName, _
                    OpenAfterPublish:=False
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 23, 2014 7:59 PM
  • Thank you, Thank you very much Hans.

    You have helped me out very much.

    Thursday, October 23, 2014 9:12 PM
  • Hi Hans,

    I have used the code above previously to print to PDF, I am now trying to use in a different workbook and keep getting the following error message:

    'Run-time error '1004': Document not saved. The Document may be open, or an error may have been encountered when saving.'

    I have tried several different workbooks and receive the same message. My original document which I used this code is still operating perfectly. I would really appreciate your assistance.

    Regards.

    Thursday, November 13, 2014 4:41 PM
  • I'd have to see the workbook.

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 13, 2014 4:58 PM
  • Hello Dear Hans, 

    I am getting run time error 1004. Please check the snap. 

    Can you please help me resolving this? 

    Monday, January 5, 2015 7:14 AM
  • On which line do you get an error message? (Your screenshot didn't make it)

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 5, 2015 3:29 PM
  • Hi Hans,

    Can you help me with a code. I wish to print my current sheet to PDF. But I wish to rename it automatically with filename and current date. Can u help me??

    Wednesday, January 14, 2015 1:23 AM
  • The code could look like this:

    Sub Export2PDF()
        Dim strFileName As String
        ' Change path and filename as desired
        strFileName = "C:\MyFolder\MySubfolder\" & ActiveSheet.Name & "_" & _
            Format(Date, "yyyymmdd") & ".pdf"
        ' Export active sheet to PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, OpenAfterPublish:=False
    End Sub

    Change the path as needed; you can specify a different format for the date, etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 14, 2015 1:36 AM
  • Hans,

    This thread has helped me a lot. I'm using the button Save as pdf and it works fine.

    Is there a way that i can save a pdf of a worksheet according to the print margin? for example A4 paper dimensions?

    Now i get a large pdf with a lot of white empty space around my data.

    Is there a way to save either the print margin or a cell range?  The cell range would be rectangular for let's say A1-N1 up to A41-N41

    Wednesday, January 14, 2015 5:32 PM
  • If you want to export a specific range such as A1:N41 to PDF, you can change the line

        ActiveSheet.ExportAsFixedFormat ...

    to

        Range("A1:N41").ExportAsFixedFormat ...

    If you want to export a single page, for example the first page of the active sheet:

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename
    :=strFileName, OpenAfterPublish:=False, From:=1, To:=1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 14, 2015 7:46 PM
  • Hi Hans,

    I have the same Kind of Requirement, Could you please look into this for me please.

    1) In My Excel file I have 40 sheets with Name (Summary Sheet, Sheet 1, Sheet 2...Sheet 39)
    2) I want to export only the Summary Sheet to PDF format.
    3) In Summary sheet I have applied formula upto 300 rows, So Based on formula - Sometime all rows will show data and sometimes it shows less than 300 rows data. It all depends on the formula I applied.
    5) When I export to PDF, the output is showing all 300 rows even if we don't have data in those rows.

    Basically I want to export and Email the Summary Sheet to PDF which has data in the rows.

    Please help me on this!

    Thanks!

    Kiran

    Monday, January 19, 2015 6:58 PM
  • Reposted as a separate thread in Need to Export the Summary Sheet of EXCEL which has data for the ROWS to PDF format. Please see my reply there.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 19, 2015 8:23 PM
  • You could use code like this:

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Hi Hans,

    Really great reading through all this stuff, I have used the code above however changes "ActiveSheet" to a "Worksheets("Sheet1").Range("A1:P40") which works fine until i hide Sheet1. After i hide it my button works until the point of giving the file a name and then has the following error:

    Run-time error '5':

    Invalid procedure call or argument

    Could you suggest a way I could call on a hidden worksheet?

    Thanks!

    EDIT - anyone that may come accross this - i made it work using the following code:

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="\\server\share\folder\", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            Application.ScreenUpdating = False
            Sheets("Sheet1").Visible = True
            Worksheets("Sheet1").Range("A1:P33").ExportAsFixedFormat Type:=xlTypePDF, _
             Filename:=strFileName, _
                OpenAfterPublish:=False
            Sheets("Sheet1").Visible = False
        End If
    End Sub

    • Proposed as answer by E.Pop Wednesday, February 18, 2015 12:33 AM
    • Unproposed as answer by E.Pop Wednesday, February 18, 2015 12:33 AM
    • Edited by E.Pop Wednesday, February 18, 2015 12:34 AM
    • Proposed as answer by E.Pop Wednesday, February 18, 2015 12:36 AM
    Wednesday, February 18, 2015 12:07 AM
  • I used your code to put together something specific to my situation. On a single sheet (Paystub) I have payroll paystubs that I save to .pdf. Each individual paystub is a range of 22 rows and 4 columns that continue down without skipping any rows (so there is consistency). The number of paystubs I want to generate changes, and that number is in cell M1.

    I tested the code with just Case 1 and Case 2 in the code (Case 3-40 was not written in yet), and everything worked perfectly fine. So I propagated the code down to Case 40. So where Case 1 has one block of code for one paystub, and Case 2 has two blocks of code for two paystubs, it continues through to Case 40 which has 40 blocks of code for up to 40 paystubs.

    The problem is that I get an error that the procedure is too long to run. So my question is how can I shorten the code to get it to work (using a loop with offsets or something like that?). Any advice and help you could give would be much appreciated.

    Sub MyPDFGenerator()
        Dim vShts As Variant
    
        vShts = Sheets("Paystub").Range("M1")
        If Not IsNumeric(vShts) Then
            Exit Sub
        Else
                Select Case vShts
                    Case 1
    
                        Range("B3:E24").Select
                        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:="C:\Payroll\Paystubs\" & ActiveSheet.Range("G4").Value & "_Paystub_" & ActiveSheet.Range("H1").Value & ".pdf", _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                    Case 2
    
                        Range("B3:E24").Select
                        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:="C:\Payroll\Paystubs\" & ActiveSheet.Range("G4").Value & "_Paystub_" & ActiveSheet.Range("H1").Value & ".pdf", _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
                        Range("B25:E46").Select
                        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:="C:\Payroll\Paystubs\" & ActiveSheet.Range("G26").Value & "_Paystub_" & ActiveSheet.Range("H1").Value & ".pdf", _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                    
                    Case 3
    
                        Range("B3:E24").Select
                        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:="C:\Payroll\Paystubs\" & ActiveSheet.Range("G4").Value & "_Paystub_" & ActiveSheet.Range("H1").Value & ".pdf", _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
                        Range("B25:E46").Select
                        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:="C:\Payroll\Paystubs\" & ActiveSheet.Range("G26").Value & "_Paystub_" & ActiveSheet.Range("H1").Value & ".pdf", _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
                        Range("B47:E68").Select
                        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:="C:\Payroll\Paystubs\" & ActiveSheet.Range("G48").Value & "_Paystub_" & ActiveSheet.Range("H1").Value & ".pdf", _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
                End Select
        End If
    End Sub
    

    Thanks in advance!

    Saturday, February 28, 2015 11:01 PM
  • So I ended up figuring this out, and wanted to post back to share in case anyone else might benefit from it.

    Sub PayStubGenerator()
    
    Dim vNumPaystubs As Variant
    Dim R As Integer
    
    vNumPaystubs = Sheets("Paystub").Range("G1")
    
        For x = 1 To vNumPaystubs Step 1
    
            R = (x - 1) * 22
            Range("B3:E24").Offset(R, 0).Select
    
            Selection.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\Payroll\Paystubs\" & ActiveSheet.Range("G4").Offset(R, 0).Value & "_Paystub_" & ActiveSheet.Range("H1").Value & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
        Next
        Range("A1").Select
    End Sub
    

    Cell G1 contains the number of paystubs to generate.

    Cells B3:E24 contains the first paystub (there is a different paystub every 22 rows).

    Cell G4 contains the employee name.

    Cell H1 contains a date suffix in the form of yyyymmdd.

    Sunday, March 1, 2015 1:49 AM
  • Hi, this thread has helped me alot already, now I have one more question with this code that I have used for a while now and works very good. So I was just wondering if a code could be added to create a folder in another folder if it doesnt already exist with the name range of G3 and than save a pdf file with the name of the cell E1 in that folder. So could it be added to this code?

    Thank you.

    Sub PrintStuff()
        Dim vShts As Variant
        Dim strFileName As String
    
        vShts = Sheets(1).Range("A1")
        If Not IsNumeric(vShts) Then
            Exit Sub
        Else
            ' Change path and filename as desired
            strFileName = "C:\MyFolder\MySubfolder\MyFile.pdf"
            If strFileName <> "False" Then
                Select Case vShts
                    Case 1
                        Sheets("Sheet1").Select
                    Case 2
                        Sheets("Sheet2").Select
                    Case 3
                        Sheets(Array("Sheet1", "Sheet2")).Select
                End Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=strFileName, _
                    OpenAfterPublish:=False
            End If
        End If
    End Sub



    • Edited by JKicker Tuesday, March 3, 2015 12:43 AM
    Tuesday, March 3, 2015 12:43 AM
  • Hi,

    New to the forum but was wondering wether It's possible to automatically export an xlsx to PDF and then mail the PDF with the default mail client(wether it's Outlook or any ther mail client) of the person who wants to mail the PDF?

    Thanks in advance,

    Sam

    Wednesday, March 11, 2015 11:59 AM
  • JKicker, does this do what you want?

    Sub PrintStuff()
        Dim vShts As Variant
        Dim strPath As String
        Dim strFileName As String
    
        vShts = Sheets(1).Range("A1")
        If Not IsNumeric(vShts) Then
            Exit Sub
        Else
            ' Change path and filename as desired
            strPath = "C:\MyFolder\" & Sheets(1).Range("G3")
            If Dir(strPath, vbDirectory) = "" Then
                MkDir strPath
            End If
            strFileName = strPath & "\" & Sheets(1).Range("E1") & ".pdf"
            If strFileName <> "False" Then
                Select Case vShts
                    Case 1
                        Sheets("Sheet1").Select
                    Case 2
                        Sheets("Sheet2").Select
                    Case 3
                        Sheets(Array("Sheet1", "Sheet2")).Select
                End Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=strFileName, _
                    OpenAfterPublish:=False
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 11, 2015 3:18 PM
  • @Sam Plessers:

    Not as far as I know. Mail clients such as Windows (Live) Mail do not have a programming interface that let you attach a file to an e-mail message. You need Outlook for that.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 11, 2015 3:30 PM
  • Hi Hans Vogelaar,

    I am not sure, if I can ask it here or start new thread. I want to ask you. Hence posting here. Please forgive if doing it wrong.

    I use a code to make sheets of print size A4 from a big sheet. They get printed out and deleted from workbook. I want them to be converted to pdf then printed out and then get deleted. Is it possible ?

    This is code-

    Sub Oval1_Click()
        ScreenUpdating = False
        Application.DisplayAlerts = False
        Sheets("PERMANENT ARTICLES").Visible = True
        Sheets("PERMANENT ARTICLES").Copy After:=Sheets(1)
        Sheets("PERMANENT ARTICLES (2)").Name = "PERMANENT ARTICLES (1)"
        
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0)
            .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlPortrait
            .Draft = False
            .PaperSize = xlPaperA4
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = 100
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        
        Application.PrintCommunication = True
        ActiveWindow.View = xlPageLayoutView
        ActiveWindow.DisplayWhitespace = False
        Sheets("PERMANENT ARTICLES (1)").Copy After:=Sheets(2)
        Sheets("PERMANENT ARTICLES (2)").Copy After:=Sheets(3)
        Sheets("PERMANENT ARTICLES (3)").Copy After:=Sheets(2)
        Sheets("PERMANENT ARTICLES (4)").Copy After:=Sheets(2)
        Sheets("PERMANENT ARTICLES (5)").Copy After:=Sheets(2)

        Sheets("PERMANENT ARTICLES (1)").Select
        Sheets("PERMANENT ARTICLES (1)").Rows("63:449").Delete Shift:=xlUp
        ActiveCell.FormulaR1C1 = "='PERMANENT ARTICLES'!R[197]C"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Sheets("PERMANENT ARTICLES (1)").Visible = False

        Sheets("PERMANENT ARTICLES (2)").Select
        Sheets("PERMANENT ARTICLES (2)").Rows("1:3").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (2)").Rows("7:59").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (2)").Rows("55:259").Delete Shift:=xlUp
        ActiveCell.FormulaR1C1 = "='PERMANENT ARTICLES'!R[205]C"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Sheets("PERMANENT ARTICLES (2)").Visible = False

        Sheets("PERMANENT ARTICLES (3)").Select
        Sheets("PERMANENT ARTICLES (3)").Rows("1:3").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (3)").Rows("7:107").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (3)").Rows("53:259").Delete Shift:=xlUp
        ActiveCell.FormulaR1C1 = "='PERMANENT ARTICLES'!R[207]C"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Sheets("PERMANENT ARTICLES (3)").Visible = False

        Sheets("PERMANENT ARTICLES (4)").Select
        Sheets("PERMANENT ARTICLES (4)").Rows("1:3").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (4)").Rows("7:153").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (4)").Rows("52:259").Delete Shift:=xlUp
        ActiveCell.FormulaR1C1 = "='PERMANENT ARTICLES'!R[209]C"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Sheets("PERMANENT ARTICLES (4)").Visible = False

        Sheets("PERMANENT ARTICLES (5)").Select
        Sheets("PERMANENT ARTICLES (5)").Rows("1:3").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (5)").Rows("7:198").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (5)").Rows("53:259").Delete Shift:=xlUp
        ActiveCell.FormulaR1C1 = "='PERMANENT ARTICLES'!R[208]C"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Sheets("PERMANENT ARTICLES (5)").Visible = False

        Sheets("PERMANENT ARTICLES (6)").Select
        Sheets("PERMANENT ARTICLES (6)").Rows("1:3").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (6)").Rows("7:244").Delete Shift:=xlUp
        Sheets("PERMANENT ARTICLES (6)").Rows("53:259").Delete Shift:=xlUp
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Sheets("PERMANENT ARTICLES (6)").Visible = False

        Sheets("PERMANENT ARTICLES (1)").Delete
        Sheets("PERMANENT ARTICLES (2)").Delete
        Sheets("PERMANENT ARTICLES (3)").Delete
        Sheets("PERMANENT ARTICLES (4)").Delete
        Sheets("PERMANENT ARTICLES (5)").Delete
        Sheets("PERMANENT ARTICLES (6)").Delete

        Sheets("PERMANENT ARTICLES").Visible = False
        Sheets("PHYSICAL VERIFICATION LIST (6)").Visible = True
        Sheets("PHYSICAL VERIFICATION LIST (6)").Select
        Sheets("PHYSICAL VERIFICATION LIST (7)").Visible = False
        ThisWorkbook.Save
        ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub

    Also if there is any mistake in code. Though it is working nice but vibration in screen is still there.

    regards


    Friday, April 3, 2015 2:05 AM
  • Good Day Hans,

    Your codes helped me to print to PDF.  Great, but I would simultaneously like the same PDF document to be emailed to a specific person after it being PDF'ed.

    Here is my existing code, please help with the second aspect  of emailing the pdf document.

    I am using Outlook as my default mail application.

    I look forward to your response.

    Thank you in advance.

    Rishi

    Friday, May 22, 2015 9:27 AM
  • @Rishi915:

    You didn't post your existing code, but here is an example. You'll have to modify it to suit your needs, of course.

    Sub SaveAndSendPDF()
        Dim strFile As String
        Dim olApp As Object
        Dim olMsg As Object
        ' Modify path and filename as needed
        strFile = "C:\PDF\Quotation_" & _
            ActiveSheet.Range("AY8").Value & ".pdf"
        ' Export to PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFile, OpenAfterPublish:=False
        ' Start Outlook
        On Error Resume Next
        Set olApp = GetObject(Class:="Outlook.Application")
        If olApp Is Nothing Then
            Set olApp = CreateObject(Class:="Outlook.Application")
            If olApp Is Nothing Then
                MsgBox "Cannot start Outlook", vbExclamation
                Exit Sub
            End If
        End If
        On Error GoTo ErrHandler
        olApp.Session.Logon
        ' Create new mail message
        Set olMsg = olApp.CreateItem(0) ' 0 = olMailItem
        ' Specify recipient
        olMsg.To = "you@somewhere.com"
        ' Specify subject
        olMsg.Subject = "This is a test message"
        ' Specify body text
        olMsg.Body = "Hello there!" & vbCrLf & _
            "Please see the attached PDF file." & vbCrLf & _
            "Cheers," & vbCrLf & _
            "(name of sender)"
        ' Attach file
        olMsg.Attachments.Add strFile
        ' Display the message
        ' If you want to send immediately, replace Display with Send
        olMsg.Display
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, May 22, 2015 11:38 AM
  • Hi Hans,

    I was using the code you posted for saving to a PDF (Very helpful!!)
    I've placed the macro on a particular sheet amongst my other worksheets (#3).
    When the macro is clicked it outputs the PDF for that worksheet. However, if I want the other worksheets
    as in, the ones AFTER my current work-sheet to also be output how would I do that?

    Just to clarify, I have 5 worksheets in my spreadsheet.

    Clinicians click my macro 'Enter New Client' and this creates a new tab. They can create multiple new client records. I want to export into a PDF all the tabs which are client records.

    The code I use for this creation is: 

      Sheets("Template").Copy After:=Sheets("Template")
        Sheets("Template (2)").Visible = True
        Sheets("Template (2)").Name = ClientAbbrev

    I drafted up some pseudo-code like:
    Dim Sht as Worksheet

    For Each Sht In ThisWorkbook.Worksheets

    If Application.Proper(sht.Name) = Application.proper(worksheetName) THEN
    Worksheet exists = True

    //Somehow Export report into the one PDF 

    Would you be able to help me out with this please?

    Thank you! 


    Tuesday, June 16, 2015 5:50 AM
  • Hi Hans,

    I was using the code you posted for saving to a PDF (Very helpful!!)
    I've placed the macro on a particular sheet amongst my other worksheets (#3).
    When the macro is clicked it outputs the PDF for that worksheet. However, if I want the other worksheets
    as in, the ones AFTER my current work-sheet to also be output how would I do that?

    Just to clarify, I have 5 worksheets in my spreadsheet.

    Clinicians click my macro 'Enter New Client' and this creates a new tab. They can create multiple new client records. I want to export into a PDF all the tabs which are client records.

    The code I use for this creation is: 

      Sheets("Template").Copy After:=Sheets("Template")
        Sheets("Template (2)").Visible = True
        Sheets("Template (2)").Name = ClientAbbrev

    I drafted up some pseudo-code like:
    Dim Sht as Worksheet

    For Each Sht In ThisWorkbook.Worksheets

    If Application.Proper(sht.Name) = Application.proper(worksheetName) THEN
    Worksheet exists = True

    //Somehow Export report into the one PDF 

    Would you be able to help me out with this please?

    Thank you! 

    Tuesday, June 16, 2015 6:55 AM
  • Try code like this:

    Sub SavePDF()
        Dim wsh As Worksheet
        Dim i As Long
        Set wsh = ActiveSheet
        For i = wsh.Index + 1 To Worksheets.Count
            Set wsh = wsh.Next
            wsh.Select Replace:=False
        Next i
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\Export.pdf", _
            OpenAfterPublish:=False
    End Sub

    The code will select the active sheet together with all sheets following it, and export those sheets to a single PDF file.

    Modify as needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 16, 2015 3:30 PM
  • Hi Hans,

    I gave the code a shot but it came up with a debug error on the line:
    Wsh.Select Replace:=False.

    This is what I have now: 

    Sub SavePDF()
        Dim wsh As Worksheet
        Dim i As Long
        Set wsh = ActiveSheet
        For i = wsh.Index + 1 To Worksheets.count
            Set wsh = wsh.Next
            wsh.Select Replace:=False
        Next i
           Dim strFileName As String
           strFileName = Application.GetSaveAsFilename( _
           FileFilter:="PDF Files (*.pdf),*.pdf", _
           Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, _
            OpenAfterPublish:=True
        End If
    End Sub

    Wednesday, June 17, 2015 7:53 AM
  • Do you have hidden worksheets after the active sheet?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 17, 2015 1:39 PM
  • Hi Hans,

    I certainly do. I moved the hidden work-sheets and tried the function again and it seemed to work. The only issue is that it outputs every single worksheet besides the worksheet that is active. So there are a few un-necessary worksheets included as well.
    Could this be the case because I have a macro which links the other sheets together so they're all classed as 'active'?

    Kind Regards,
    Chris
    Thursday, June 18, 2015 2:59 AM
  • I interpreted your "I want the other worksheets as in, the ones AFTER my current work-sheet to also be output" to mean that you wanted to include all sheets after the active worksheet.

    If you want something else, you will have to specify how to determine exactly which sheets to include.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 18, 2015 4:17 AM
  • Hi Hans, 

    Apologies for the confusion. This is how I currently have the worksheets arranged (including hidden ones)

    Clinical Updates. Monthly Report Hours. YTD Client Hours. List Hours (Named Range Sheet). Template Sheet. Enter New.

    So I have 6 worksheets. 
    Now when someone enters a new client in the Enter New Worksheet they enter in the name, funding program, date and acronym. As soon as they click enter new client, my macro will activate and create a copy of the template work-sheet, rename it via initial and put the information the user entered into the newly copied template.

    i.e. If Hans Vogelaar was inputted as a client, the Worksheet would be named HV.


    They can insert as many clients as necessary.

    What I would like is for the Save PDF() macro to identify these sheets with acronyms and then output them in my PDF report. I was thinking about this and as we know that each Worksheet will be named after the 2 letter acronym - this will always be the case.
    So this is what I was thinking as rough psuedo code. However I don't want to export multiple PDFs, just the one.
    For Each Worksheet in this workbook
    If the Worksheet name equals 2
    Then Export PDF
    ELSE
    Do not Export. 

    What do you think?

    Thursday, June 18, 2015 4:32 AM
  • Try this version:

    Sub SavePDF()
        Dim wsh As Worksheet
        Dim f As Boolean
        Dim strFileName As String
        f = True
        For Each wsh In Worksheets
            If Len(wsh.Name) = 2 Then
                wsh.Select Replace:=f
                f = False
            End If
        Next wsh
        strFileName = Application.GetSaveAsFilename( _
           FileFilter:="PDF Files (*.pdf),*.pdf", _
           Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 18, 2015 9:48 AM
  • Fantastic! It works like a charm!
    Thank you so much Hans! :)!!
    Friday, June 19, 2015 1:08 AM
  • Hi Hans,

    I am new to VBA, could you please help me with VB code or any simple method to save my active sheet as different PDFs with their names based on value in a particular cell.

    For example, if cell A1 of active sheet has serial no. 1, it should automatically save active sheet as PDF in a given folder with name as "Report_1".. But i have serial no.s from 1 to 5000 which is difficult for saving individual PDFs.

    So i am looking for the solution to save all PDFs at once [bulk download] with respective names as per cell vale in A1.. 

    Kindly help..

    Tuesday, August 4, 2015 9:05 AM
  • Hi Hans,

    First of all I really apologize if I am going to ask something stupid.

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\Quotation_" & _
            ActiveSheet.Range("AY8").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    I have tried your above suggested code and it is really working great. But now I have a new requirement and I am pretty much sure that you can help me out or guide me to find out a good solution. 

    I want to save PDF file in a specific format like "MR_invoice number_client name_month.pdf". Where MR is a constant, Invoice number is an incremental number, client name and month. All these 4 values are coming from four different cells. You can say "MR_E4_P4+1_P12_J12.pdf".

    I will really appreciate if you can help me out in this. I also want to ask can I generate a PDF file of A.Xlsx from a button which is placed in B.xlsx.

    Regards, 

    Vicky

    Tuesday, August 4, 2015 10:02 AM
  • @VickyHanif: I have posted an extensive reply in your thread Excel to PDF macro.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 4, 2015 3:17 PM
  • Hi Hans

    Apologies for the basic question, but I can't figure out how to do this myself and have searched with no luck.

    I am using the following code:

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="T:\Maths\Data 2015-2016\Year 11 November Mock PLC\" & _
            ActiveSheet.Range("C3").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    This works fine, but to arrange the pdf files neatly, I want the files to save in the following folder:

    T:\Maths\Data 2015-2016\Year 11 November Mock PLC\(name from Cell C2).

    Is this possible?

    Thank you in advance

    Q

    Sunday, November 29, 2015 10:00 PM
  • That would be

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="T:\Maths\Data 2015-2016\Year 11 November Mock PLC\" & _
            ActiveSheet.Range("C2").Value & "\" & _
            ActiveSheet.Range("C3").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 29, 2015 10:46 PM
  • You, sir, are an absolute legend. Thank you.

    I'm not too fussed about this bit, but would be an added bonus.

    The value in C3 is a dropdown list, which in turn populates a table.

    Is it at all possible to make the macro button generate pdf files for each value in C3. I can send you the file if this isn't making any sense.

    Sunday, November 29, 2015 10:58 PM
  • Let's say that the source list of the dropdown in C3 is the range K2:K18 on Sheet2. You could use

    Sub SavePDF()
        Dim cel As Range
        For Each cel In Worksheets("Sheet2").Range("K2:K18")
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:="T:\Maths\Data 2015-2016\Year 11 November Mock PLC\" & _
                ActiveSheet.Range("C2").Value & "\" & _
                cel.Value & ".pdf", _
                OpenAfterPublish:=False
        Next cel
    End Sub

    Modify the sheet name and range as needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 30, 2015 6:37 AM
  • Ok, this is where it gets complicated I guess.

    The source for C3 is dependant upon another dropdown list which is in C2.

    i.e. i have a dropdown list in C2 which is sourced from Sheet 3 A1:A12.

    This then modifies the dropdown list in C3, which could be sourced from any list in Sheet 3 from B1:B30 to M1:M30

    Monday, November 30, 2015 8:23 PM
  • It would have been better if you had provided the relevant information at the outset instead of changing the requirements after each reply.

    Sub SavePDF()
        Const strPath = "T:\Maths\Data 2015-2016\" & _
            "Year 11 November Mock PLC\"
        Dim wshL As Worksheet
        Dim wshA As Worksheet
        Dim rngL1 As Range
        Dim rngL2 As Range
        Dim cel1 As Range
        Dim cel2 As Range
        Dim cel As Range
        Dim lngOffset As Long
        Dim strFolder As String
        Dim strFile As String
        Set wshL = Worksheets("Sheet3")
        Set rngL1 = wshL.Range("A1:A12")
        Set rngL2 = wshL.Range("A1:A30")
        Set wshA = ActiveSheet
        Set cel1 = wshA.Range("C2")
        Set cel2 = wshA.Range("C3")
        strFolder = cel1.Value
        lngOffset = Application.Match(cel2.Value, rngL1, 0)
        For Each cel In rngL2.Offset(0, lngOffset)
            strFile = cel.Value
            wshA.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strPath & _
                    strFolder & "\" & _
                    strFile & ".pdf", _
                OpenAfterPublish:=False
        Next cel
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 30, 2015 9:33 PM
  • Apologise for that. It was more an issue of me not knowing what could be done and how to explain my requirements.

    Your help is much appreciated.

    Monday, November 30, 2015 10:13 PM
  • Dear Mr. Hans,

    Your solution above and answers for most of the questions were phenomenal learning experience for me.

    I am using the above example for my project, and i am finding it very difficult to find the solution.

    In the above example, in case 3: instead of printing two sheets cascaded, i am in need of only( sheet 1 + a few rows (A1 to N30) in sheet 2) to be printed as single PDF.Can you please guide me?

    Thank you very much for your answers


    • Edited by PKumar66 Monday, December 7, 2015 7:49 AM
    Friday, December 4, 2015 2:01 PM
  • It'd be easiest to copy the rows from Sheet2 that you need to Sheet1, ans export only Sheet1 to PDF.

    Alternatively, set the Print Area of Sheet2 to the range A1:N30, and export Sheet1 and Sheet2.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, December 4, 2015 5:27 PM
  • Dear Mr. Hans,

    Thank you for your reply. There is images and data in sheet 2 which when i try to copy to sheet 1 using formula didn't work. The second sheet contains many subsections A1:N30 belongs to product A. A31: to N60 belongs to product B. Depending on type of selection, i append part of Sheet 2 to sheet 1. I hope, i have clearly mentioned the challenge i am facing. Can you please suggest any other alternatives? I appreciate your help in this topic.

    Best Regards

    • Edited by PKumar66 Monday, December 7, 2015 7:48 AM
    Monday, December 7, 2015 7:07 AM
  • Have you tried setting the Print Area of Sheet2 to the area that you want to export to PDF, then select Sheet1 and Sheet2 and export them to PDF?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 7, 2015 4:04 PM
  • Dear Mr. Hans,

    Yes. I tried printing the sheet 2 and sheet 1 via array function. I was able to see sheet 1 and part of sheet2 in same pdf. But, now i understand my real problem. If i am unable to set the printing area dynamically. If i am able to that, then i will be able to print the selected area in the sheet2 easily. Can you help me in this? Setting a print area dynamically based on a condition.

    Thank you very much for your reply.

    Thursday, December 10, 2015 7:27 AM
  • Without knowing the details it's difficult to provide detailed instructions. The code could look similar to this:

        Dim Product As String
        Dim Offs As Long
        Product = "..." ' could be a cell value, for example
        ' Modify and expand as needed
        Select Case Product
            Case "A"
                Offs = 0
            Case "B"
                Offs = 30
            ' ...
        End Select
        Worksheets("Sheet2").PageSetup.PrintArea = _
            Range("A1:N30").Offset(Offs).Address


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by PKumar66 Friday, December 11, 2015 12:35 PM
    Thursday, December 10, 2015 4:04 PM
  • Dear Mr. Hans,

    Your solution was perfect. Thank you!

    I am very grateful for you.

    Best Regards

    Friday, December 11, 2015 12:35 PM
  • Hello I used this:

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="D:\Customer quotes\Kaspersky\" & _
            ActiveSheet.Range("B2").Value & "\" & _
            ActiveSheet.Range("B4").Value & ".pdf", _
            OpenAfterPublish:=True
    End Sub

    But is not working, i want to print to pdf with the name on B2 and B4 so to appear like Kaspersky_Partner Name_End User Name

    Please help

    • Proposed as answer by Andriver90 Tuesday, December 15, 2015 5:40 PM
    Tuesday, December 15, 2015 2:36 PM
  • Does this do what you want?

    (By the way, why did you propose your question as the answer?)

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="D:\Customer quotes\Kaspersky_" & _
            ActiveSheet.Range("B2").Value & "_" & _
            ActiveSheet.Range("B4").Value & ".pdf", _
            OpenAfterPublish:=True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 15, 2015 8:17 PM
  • Hi Hans,

     

    I was hoping you could help me, I been going through the code on this page, but can’t seem to find what I am looking for. Is there a code that can make me print two separate excel sheets, into one pdf? And a way to sort them?

    Thank you for all the help you are giving here! 

    Friday, January 8, 2016 1:41 PM
  • No clue if it's possible with VBA but I know PDFCreator allows you to combine multiple objects into a single PDF and play with the order (and many other features). It's free/open source, works great and may even have an API you can use.
    Friday, January 8, 2016 4:09 PM
  • Hello Hans,

    I want to create a macro that will help me copy an entire sheet from a .csv file to an xlsm workbook that already comprises multiple sheets.

    In fact, what I want to do is the following:

    - let's say that I already have a xlsm file opened with multiple sheets

    - I want to create a macro here that is going to search for a .csv file in a predefined path

    - the search is going to be based on a cell value of the xlsm file

    -after the search is complete, I want the macro to open that file (the .csv one), copy a sheet from it into the xlsm file and then close the .csv file.

    Could you please help me?

    Stefan

    Monday, January 11, 2016 12:36 PM
  • @Stefan Ungureanu: This has nothing to do with the original question. Please start a new thread and ask your question there.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 11, 2016 5:14 PM
  • Dear Hans

    I would like once i print my excel sheet into pdf file to have a picture as a background.

    Is it possible to be done?

    Friday, January 22, 2016 12:19 PM
  • I don't think so.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 22, 2016 1:35 PM
  • Hi Hans,

    New to the forum:

    I was wondering how I would create a macro that would save all sheets in a workbook and save it to a pdf file?

    EX: I have 5 sheets in a workbook, and want it to create 1 PDF file with all 5 sheets in it.

    I have used this code to create the first sheet in the workbook:

    Sub SavePDF()
        Dim wsh As Worksheet
        Dim f As Boolean
        Dim strFileName As String
        f = True
        For Each wsh In Worksheets
            If Len(wsh.Name) = 2 Then
                wsh.Select Replace:=f
                f = False
            End If
        Next wsh
        strFileName = Application.GetSaveAsFilename( _
           FileFilter:="PDF Files (*.pdf),*.pdf", _
           Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=True
        End If
    End Sub
    

    Thanks in advance!!!

    Wednesday, May 25, 2016 8:02 PM
  • If you want to save the entire workbook as PDF, the code is simpler:

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
           FileFilter:="PDF Files (*.pdf),*.pdf", _
           Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, May 26, 2016 2:22 AM
  • HANS!!!!

    You are a life saver! Thank you!

    Thursday, May 26, 2016 3:21 PM
  • Thank You Hans, with the code you mentioned above, I could save my invoices easily into PDF files.

    I have couple more doubts out of the scope of this thread subject. 

    1. I have a column Quantity in my invoice sheet. The Quantity column is a drop down list which has values 1 to 10. 

    I have the sheet in the following way

    Sl. No , Particulars, Rate, Quantity, Amount

    When I select a value in Particulars (drop down list), the Rate field automatically populates corresponding values (from table). I have kept Quantity also as a drop down list with values from  1 to 10. However, I want Quantity field also to populate automatically '1' as default value when a value is selected in Particulars. When I change value in quantity, it should accept the changed value. Additionally, I have inserted a button which when clicked clears values in Particulars range. Which in turn clears values in Rate and Amount fields. I want the Quantity field also to be cleared. This happens if values are not changed in Quantity cell range, but if any value in any of the cells in Quantity range is changed, those cells are not cleared.  I want all values to be cleared and formulas to be re initiated for a fresh invoice.

    2. I want the SL. No field (which is the first column) to be automatically filled (incremental value) as and when values are selected in Particulars range (second column). That is if i select an item in the first row of the Particulars column, first row of Sl.No should get 1 as its value. When I select an item in the 2nd row of the Particulars column, second row of Sl.No should get 2 as its value and so on. I have given option to add upto 10 items (rows) in the invoice.

    These are the two items pending in my sheet. If you want to see the sheet I can mail it to you.

    Please help me.

    Monday, June 6, 2016 6:24 PM
  • When I am following this step and clicking the button to generate PDF File it is showing ""Run-time error 1004"".. Document not saved. The document may be open, or an error may have been encountered when saving.

    Kindly let me know how to resolve this issue.

    Wednesday, June 8, 2016 9:37 AM
  • Hans is the best person to reply to this.

    However when I encountered this error, I corrected it by giving the right path.

    The path where your file is to be saved. Try changing your path. The FILENAME="C:\....." should be an existing path. Give your existing folder path.

    Pramod


    Wednesday, June 8, 2016 2:00 PM
  • This has nothing to do with the subject of this thread. Please start a new thread.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, June 11, 2016 12:23 AM
  • Hi Hans,

    I like your simple code and works well.

    I would like your assistance to include a few lines that would have the following out put;

    Save excel worksheet to PDF, with an invoice number that automatically accumulates after invoice is saved to PDF in a specific location.  

    Please see this code below as I am invoice number on excel worksheet accumulates but does not save a pdf file to that location.

    Sub NextInvoice()
    Range("E5").Value = Range("E5").Value + 1
    End Sub

    Sub saveInvWithNewName()
    Dim NewFN As Variant
    ' Copy Invoice to a new workbook
    ActiveSheet.Copy
    NewFN = "C:\PDF\RITOS2015-00" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
    End Sub

    Regards,

    Richard

    Thursday, June 23, 2016 12:06 AM
  • Hi Hans,

    This code works ok but file is saved in excel format with invoice number accumulating after files is saved in PDF which I would like the exact formula but files to be save in PDF instead.

    My apologies I am new to this VB Coding stuff and have been doing research on the internet to find a specific code to give me the outcome I prefer so I hope this will help;

    ------------------------------------------------------------------

    Sub NextInvoice()
    Range("E5").Value = Range("E5").Value + 1
    End Sub

    Sub saveInvWithNewName()
    Dim NewFN As Variant
    ' Copy Invoice to a new workbook
    ActiveSheet.Copy
    NewFN = "C:\PDF\RITOS2016-00" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
    End Sub

    ----------------------------------------------------------------------------

    Regards,

    Richard

    Thursday, June 23, 2016 12:17 AM
  • The lines

    NewFN = "C:\PDF\RITOS2016-00" & Range("E5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook

    save the sheet as an Excel workbook. Change them to

    NewFN = "C:\PDF\RITOS2016-00" & Range("E5").Value & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename
    :=NewFN, OpenAfterPublish:=False


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 23, 2016 5:41 AM
  • hi

    Grateful if you can help. I've used your code above which works great but now I want to be able to select the sheets (I have multiple sheets in my workbook) that I need to save as pdf by pressing the usual Ctr key and then just click my created Save PDF button ( based on the macro below).

     The names of the sheets can change, so don't want the code  to specify exactly. thanks

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="D:\danielle", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
           
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub

    Sunday, January 15, 2017 2:04 AM
  • @eusunt: The code that you mention will export all selected worksheets to PDF, not just the active sheet.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 15, 2017 10:42 AM
  • awesome, thanks
    Sunday, January 15, 2017 5:46 PM
  • hi Hans

    Could you possibly please advise how to change the following code so that: after i press the button too"save PDF" to allow me to see the file and if I'm happy with everything then to have another button which i can click to email the PDF to someone else in outlook? appreciate your help.

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="D:\danielle", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub

    Sunday, January 15, 2017 6:24 PM
  • Sorry to bother, any chance you could help with my previous query? I needthis for my presentation tomorrow and getting panicked a little as I so need this feature.. thanks

    Could you possibly please advise how to change the following code so that: after i press the button too"save PDF" to allow me to see the file and if I'm happy with everything then to have another button which i can click to email the PDF to someone else in outlook? appreciate your help.

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="D:\danielle", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=False
        End If
    End Sub

    Monday, January 16, 2017 9:24 AM
  • Try this:

    Sub SavePDF()
        Dim strFileName As String
        Dim objApp As Object
        Dim objMsg As Object
        strFileName = Application.GetSaveAsFilename( _
            InitialFileName:="D:\danielle", _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=True
            Set objApp = CreateObject("Outlook.Application")
            Set objMsg = objApp.CreateItem(0)
            objMsg.To = "emailaddresshere"
            objMsg.Subject = "this is the subject"
            objMsg.Body = "this is the message"
            objMsg.Attachments.Add strFileName
            objMsg.Display
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 16, 2017 3:37 PM
  • it works, thank you so much. You're a star!
    Monday, January 16, 2017 5:50 PM
  • Hello! this macro works on my personal windows computer, but in my office I'm working on Mac IOs... Could you help me to configure this macro for Excel on IOS ?

    Gabriel

    Monday, January 30, 2017 10:06 AM
  • Excel for iOS does not support VBA, so it's not possible to run a macro on an iPhone or iPad. The same goes for Android. Macros only run on Windows and MacOS.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, January 30, 2017 4:15 PM
  • Ok, but is there a formule or anything to use a Windows file with macros on a MacOS ?

     
    Sunday, February 5, 2017 3:03 PM
  • If you have Excel 2011 or later on MacOS, a workbook with macros created in Excel for Windows should run OK.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 5, 2017 4:43 PM
  • Hi Hans,

    Based on reading through this thread, I've been able to compose a macro that, based on a cell value (controlled by a dropdown), select a page in the workbook and save and generate a PDF. So, if a specific cell value = 1, a specific page in the workbook would be selected and a PDF is saved and generated.

    However, I need to be able to select multiple sheets at once, and that would all be in the same PDF. So, as an example, if A1=1, B1=1, then the sheets that I specify that is related to A1 and B1 will be selected (using vShts), and a PDF would be generated.

    Would anyone be able to help be out with this? Below is the current code I am using.

    Sub Pricing()
        Dim vShts As Variant
        Dim strFileName As String

        vShts = Sheets(1).Range("N31")
        If Not IsNumeric(vShts) Then
            Exit Sub
        Else
            strFileName = Application.GetSaveAsFilename( _
                InitialFileName:="C:\Documentations\DemoDocs\testing", _
                FileFilter:="PDF Files (*.pdf),*.pdf", _
                Title:="Save As PDF")
            If strFileName <> "False" Then
                Select Case vShts
                    Case 1
                        Sheets("DA Pricing").Select
                    End Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=strFileName, _
                    OpenAfterPublish:=True
            End If
        End If
    End Sub

    Friday, March 24, 2017 8:35 PM
  • Can you explain

    • Which cells are involved?
    • How do the cells relate to sheet names? Your code has the name 'DA Pricing' hard-coded...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 25, 2017 12:13 AM
  • Hi Hans,

    Absolutely. The entire range of cells that dictate which pages are selected for the resulting PDF file is N31:N68. Currently, the example code above is one of many macros designed to represent one sheet of the workbook at a time. So, if cell N31 is "1", then the 'DA Pricing' sheet is selected and the resulting PDF is created. The next macro (same base code) would reference, say N32, then select 'DA Pricing 2' and create the resulting PDF. I have all of these similar macros (for each sheet) being called into a separate macro. The issue is with that kind of design, is that it only creates the PDF for the very last sheet that is selected. It does not 'hold' the selections of multiple sheets. That is my real issue.

    Eric

    Monday, March 27, 2017 3:24 PM
  • Does your combined macro prompt for a filename for each individual sheet? If so, you should provide a different name each time. If not, each file you create will overwrite the previous one...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, March 27, 2017 3:48 PM
  • The prompt for a new filename doesn't happen in the combined macro, but in the individual macros:

    strFileName = Application.GetSaveAsFilename( _
                InitialFileName:="C:\Documentations\DemoDocs\testing", _
                FileFilter:="PDF Files (*.pdf),*.pdf", _
                Title:="Save As PDF")

    So when I run the combined macro, a new prompt to save each new file does occur. But, I need one PDF that contains all of the pages.

    If I set it up as such below in the combined macro, how can I make the code remember each individual sheet and make one PDF?

    Sub Proposal()
    Call DAPricing1
    Call DAPricing2
    End Sub

    Wednesday, March 29, 2017 1:58 PM
  • You can't do this by running a series of macros, you'll have to create a single macro, but it's still not clear to me what the relationship between the cells and the sheets is.

    Since this thread is already extremely long, I suggest that you start a new thread, and try to explain as clearly as you can what you want to accomplish, in detail.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 29, 2017 3:39 PM
  • Hey there,

    I think my problem fits here. I've been wanting to modify a code that I have without any success. I would like to change the information that appears in the footer of the pdf. Does anyone know where in the code I can change that. I need to change the name "Paraiso" to a different one, let's say "Love Park". 

    I'm so frustrated, and just totally block by now. Please help!!!!

                                                                       

    Sub getlogindata()

        Dim oApp As Object
        Dim oMail As Object
        Dim printform As String
        Dim datecell As String
        Dim count1 As String

        Worksheets("TiempoUsuario").Activate

        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Paste Destination:=Worksheets("TiempoUsuario").Range(Cells(erow, 1), Cells(erow, 3))

        ActiveWorkbook.Save

        Worksheets("CuentaDiario").Activate

        Range("G2").Value = Range("G2").Value + 1

        datecell = Format(Date, "dd-mm-yyyy")
        count1 = Range("G2").Value

        printform = "C:\Users\HP\Documents\casanova\CuentaDiario_" & Range("A2") & "_" & datecell & "_" & count1 & ".pdf"

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            printform, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            True

        Application.ScreenUpdating = False

        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)

        With oMail
            .To = "mymail@gmail.com"
            .Subject = "Report No. " & count1
            .body = "Daily report"
            .Attachments.Add printform
            .Send
            '.Display
        End With

        Application.ScreenUpdating = True

        ActiveSheet.PrintOut

        Range("A3:F2000").Select
        Selection.Clear

        Worksheets("LoginInfo").Activate



    End Sub



    kirv-biosapio

    Thursday, April 27, 2017 7:37 AM
  • Just above the line with ActiveSheet.ExportAsFixedFormat, insert

        ActiveSheet.PageSetup.CenterFooter = "Love Park"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by biosapio Thursday, April 27, 2017 9:59 AM
    Thursday, April 27, 2017 9:36 AM
  • Thanks man! Worked as a charm!!!!

    Cheers!!!


    kirv-biosapio

    Thursday, April 27, 2017 10:00 AM
  • Hello Gentlemen, could you please provide some help? 

    I need to create a PDF from the results that I have from a Pivot table. Everytime that I press this button it must create a different PDF with as title the name written in a specific cell with also the mm/dd/yyyy if possible(I guess using another single cell with it like how it is suppose to be for the first name of the file).

    Now I'm using the one that you shared with us all:

    Sub SavePDF()         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _         Filename:="C:\Users\***********\Desktop\Report\print.pdf", _        

    OpenAfterPublish:=False

    End Sub


    Any suggestion related on how to do that? My final goal is to have those PDF online shared with my friends and giving to everyone the chance to upload those datas and also to check if somebody has performed more checks without repeating the task more times, wasting time.

    Thank you all!

    Tuesday, May 23, 2017 3:39 PM
  • Let's say you have the title in cell B2.

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\Users\<username>\Desktop\Report\" & _
            Range("B2").Value & "_" & Format(Date, "mm_dd_yyyy") & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    I used mm_dd_yyyy as date format, since / is not allowed in file names. You can specify another format, of course.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, May 23, 2017 6:43 PM
  • It works, it's amazing!
    Is there a command to just create the PDF from a specific group of cells? it's printing 3 sheets but 2 of them are white and with no data inside.

    Thank you so much, really do appreciate the help!

    Wednesday, May 24, 2017 10:51 AM
  • Instead of ActiveSheet.ExportAsFixedFormat, you can use

    Range("B3:L50").ExportAsFixedFormat

    where B3:L50 is the range of cells that you want to export.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 24, 2017 10:57 AM
  • Hi

    I am currently using the below to create individual pdf files (each time a new value is put into C4, a new pdf with different data is generated). Is there a way that all of these pdf can be saved into one file?

    Thank you in advance for your help

    Q



    Sub moveselection()
    Dim i As Integer


    For i = 1 To 108


    Range("C4").Value = Sheets("Sheet1").Range("A4").Offset(i, 0).Value
    ThisFile = Range("C4").Value


    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="T:\Maths\QM" & _
    ActiveSheet.Range("C4").Value & ".pdf", _
    From:=1, _
    To:=1, _
    OpenAfterPublish:=False


    Next i


    End Sub
    Wednesday, June 14, 2017 11:53 AM
  • Would you really want a PDF file with 108 pages?

    Adobe Acrobat can merge multiple PDF files into one.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 23, 2017 7:30 AM
  • The reason I want to combine them is because I need a colleague to print the file 2 pages to 1. At most, there will only be 35 pages. Is there a way I can build into the code to combine the PDF or would this need to be done separately?
    Saturday, June 24, 2017 3:32 PM
  • The following macro will copy the data to a single sheet, on a separate page for each value, export that sheet to PDF, and then delete it.

    Sub moveselection()
        Dim i As Integer
        Dim wsI As Worksheet
        Dim wsO As Worksheet
        Dim r As Long
        Dim m As Long
    
        Set wsI = ActiveSheet
        m = wsI.Cells.Find(What:="*", _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
        r = 1
        Set wsO = Worksheets.Add(After:=wsI)
    
        For i = 1 To 108
            wsI.Range("C4").Value = _
                Sheets("Sheet1").Range("A" & i + 4).Value
            wsI.Range("A1:A" & m).EntireRow.Copy
            wsO.Range("A" & r).PasteSpecial xlPasteValues
            wsO.Range("A" & r).PasteSpecial xlPasteFormats
            r = r + m
            wsO.HPageBreaks.Add Before:=wsO.Range("A" & r)
        Next i
    
        wsO.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="T:\Maths\QM_All.pdf"
    
        Application.DisplayAlerts = False
        wsO.Delete
        Application.DisplayAlerts = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, June 24, 2017 4:19 PM
  • Hello Team

    Came across this forum whilst looking for help on generating PDF from button for excel content.

    I tried the macro as suggested, however i am getting Run-Time Error 1004.

    I use Microsoft Office Standard (2016) Suite.. Any help will be highly appreciated...

    Friday, July 14, 2017 6:03 AM
  • Please provide detailed information. On which line of code does the error occur? And in which of the many versions posted in this very long thread?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 14, 2017 8:37 AM
  • Please provide detailed information. On which line of code does the error occur? And in which of the many versions posted in this very long thread?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

     

    Hello
    I am fairly new to complex excel programming but recently have to work for excel export to pdf. Happened to see this post and felt fortunate enough. I am working on version excel 2007. However, while trying to create the very first code in this post chain, I am thrown with error:  Run-time error '5': Invalid procedure call or argument. In debug mode,  highlight goes on the line ActiveSheet.ExportAsFixedFormat .....The same code works fine in 2013.

    I am trying to achieve below functionality, from experts here I would like to know if this is possible from Excel directly or need some other programming. Thanks in advance

    I have an excel sheet with data till columns DL starting from A. I need to export every row of data from excel to as one PDF. Ex: 30 rows - 30 pdfs. Also Data needs to be written in a particular format into my pdf (nearly 30 columns data into pdf).

    Say, Col A, Col B, Col C has data Employee ID, Employee Name, Employee Join Date,

    Col A   |   Col B   | Col C

    0001    |  John     | 08/23/2017

    0002     |  Jack     |  07/21/2016

    my pdf must be:
                          Employee Details - John
    1. Name of the Employee :   John
    2.  ID of the Employee  :   0001
    3. Date of Joining      : 08/23/2017

    Regards

    Mathangi



    Wednesday, August 23, 2017 5:49 AM
  • In the initial release of Excel 2007, export to PDF was only available through an add-in; in one of the service packs for Office 2007, it was added to the core functionality of Excel. So if the user has a fully updated version of Office 2007, the code should work.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 23, 2017 10:58 PM
  • Thank you Hans. Yes upon install PDF add-on, it was working fine. 
    Friday, September 1, 2017 8:08 AM
  •  I have use below code.But I want the file name to be picked automatically from the existing worksheet from cell address A7 only that text which is after ":" combined with","& cell address A8 that text which is after ":" i.e i.e in Nutshell,it should extract the filename from A7 after ":" &","& A8 after ":".How this can be incorporated in the below formula.

    Sub SavePDF() Dim strFileName As String strFileName = Application.GetSaveAsFilename( _ FileFilter:="PDF Files (*.pdf),*.pdf", _ Title:="Save As PDF") If strFileName <> "False" Then ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=strFileName, _ OpenAfterPublish:=False End If End Sub


    Sunday, December 24, 2017 10:44 AM
  • Try this version:

    Sub SavePDF()
        Dim strFileName As String
        strFileName = Split(Range("A7").Value, ":")(1) & "," & _
            Split(Range("A8").Value, ":")(1) & ".pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, _
            OpenAfterPublish:=False
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 24, 2017 12:39 PM
  • Taking into above condition,I want the file at "D:\Confirmation 2073-74".Also the no spaces should be in filename at left hand side i.e just before first word

    Any help in this regard is highly appreciated.



    Sunday, December 24, 2017 12:48 PM
  • Change

        strFileName = Split(Range("A7").Value, ":")(1) & "," & _
            Split
    (Range("A8").Value, ":")(1) & ".pdf"

    to

        strFileName = "D:\Confirmation 2073-74\" & _
            Trim(Split(Range("A7").Value, ":")(1)) & "," & _
            Trim(Split
    (Range("A8").Value, ":")(1)) & ".pdf"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 24, 2017 3:11 PM
  • It worked for individual sheet.With your help I am currently using master worksheet with clicking on desired command button as and when necessary which help me to create worksheets and with your code in pdf.What I want is that on clicking desired command button,I want directly in pdf which will save me a lot of time .The link to my file is at :

    https://drive.google.com/file/d/0B2l-557xsurxYTlocVRmWFF3a3ZtejF1TGVjS08ycG9Va0ZB/view?usp=sharing

    Hope you will help me.Kindly modify the module at your end.

    Monday, December 25, 2017 3:50 AM
  • Here is a modified version:

    Sub Create_From_Template_Sheet_CheckBoxes()

        Dim ws As Worksheet
        Dim wsName As String
        Dim i As Long, LastRow As Long, cbCount As Long
        Dim strFileName As String
       
        Set ws = Worksheets("Data")
        LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
       
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
       
        cbCount = 0
        For i = 5 To LastRow
            If ws.CheckBoxes(i - 4).Value = xlOn Then
            'start from first checkbox whose index number is 1. first i in the loop is 5 then i-4 is 1.
                cbCount = cbCount + 1
                Worksheets("format").Copy After:=Worksheets(Worksheets.Count)
                With ActiveSheet
                    wsName = Replace(Replace(Replace(Replace(Replace(Replace(Replace(wsName, ":", ""), "/", ""), "\", ""), "?", ""), "*", ""), "[", ""), "]", "")
                    'to ensure there are no invalid characters in sheet names
                    wsName = Left(ws.Range("B" & i).Value, 31)
                    'to ensure ws name's length is less than or equal to 31
                    .Name = wsName
                    .Range("G6").Formula = "=""Date : ""&Data!C1"
                    .Range("A10").Formula = "=""Name of Party : "" &Data!B" & i
                    .Range("A11").Formula = "=""Address : ""&Data!C" & i
                    .Range("A12").Formula = "=""DIN No : ""&Data!D" & i
                    .Range("A16").Formula = "=""This is to certify that the company has done following complementary for ""&Data!C2&"" as follows :"""
                    .Range("A18").Formula = "=Data!E4&" & """ : Rs. """ & "&" & "Data!E" & i & "&" & """/-[""" & "&" & "SpellNepalese(Data!E" & i & ")" & "&" & """]"""
                    If ws.Range("F" & i).Value > 0 Then
                        .Range("A19").Formula = "=Data!F4&" & """ Qty Balance : Rs. """ & "&" & "Data!F" & i & "&" & """/-[""" & "&" & "SpellNepalese(Data!F" & i & ")" & "&" & """]"""
                    ElseIf ws.Range("G" & i).Value > 0 Then
                        .Range("A19").Formula = "=Data!G4&" & """ Qty Balance : Rs. """ & "&" & "Data!G" & i & "&" & """/-[""" & "&" & "SpellNepalese(Data!G" & i & ")" & "&" & """]"""
                    Else
                        .Range("A19").Formula = "Qty : -NIL"
                    End If
                    strFileName = "D:\Confirmation 2073-74\" & _
                        Trim(ws.Range("B" & i).Value) & "," & _
                        Trim(ws.Range("C" & i).Value) & ".pdf"
                    .ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:=strFileName, _
                        OpenAfterPublish:=False
                    .Delete
                End With
            End If
        Next i
        If cbCount = 0 Then
            MsgBox "You did not select any companies from Data Sheet. Check at least 1 CheckBox next to company name!", vbOKOnly, "SELECT A COMPANY"
            ws.Activate
        Else
            Worksheets("master").Activate
        End If

    End Sub

    Sub Create_From_Template_Sheet()

        Dim ws As Worksheet
        Dim wsName As String
        Dim i As Long, LastRow As Long
        Dim strFileName As String
       
        Set ws = Worksheets("Data")
        LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
       
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
       
        For i = 5 To LastRow
            Worksheets("format").Copy After:=Worksheets(Worksheets.Count)
            With ActiveSheet
                wsName = Replace(Replace(Replace(Replace(Replace(Replace(Replace(wsName, ":", ""), "/", ""), "\", ""), "?", ""), "*", ""), "[", ""), "]", "")
                'to ensure there are no invalid characters in sheet names
                wsName = Left(ws.Range("B" & i).Value, 31)
                'to ensure ws name's length is less than or equal to 31
                .Name = wsName
                .Range("G6").Formula = "=""Date : ""&Data!C1"
                .Range("A10").Formula = "=""Name of Party : "" &Data!B" & i
                .Range("A11").Formula = "=""Address : ""&Data!C" & i
                .Range("A12").Formula = "=""DIN No : ""&Data!D" & i
                .Range("A16").Formula = "=""This is to certify that the company has done following complemenatary for ""&Data!C2&"" as follows :"""
                .Range("A18").Formula = "=Data!E4&" & """ : Rs. """ & "&" & "Data!E" & i & "&" & """/-[""" & "&" & "SpellNepalese(Data!E" & i & ")" & "&" & """]"""
                If ws.Range("F" & i).Value > 0 Then
                    .Range("A19").Formula = "=Data!F4&" & """ Qty Balance : Rs. """ & "&" & "Data!F" & i & "&" & """/-[""" & "&" & "SpellNepalese(Data!F" & i & ")" & "&" & """]"""
                ElseIf ws.Range("G" & i).Value > 0 Then
                    .Range("A19").Formula = "=Data!G4&" & """ Qty Balance : Rs. """ & "&" & "Data!G" & i & "&" & """/-[""" & "&" & "SpellNepalese(Data!G" & i & ")" & "&" & """]"""
                Else
                    .Range("A19").Formula = "Qty Balance : -NIL"
                End If
                strFileName = "D:\Confirmation 2073-74\" & _
                    Trim(ws.Range("B" & i).Value) & "," & _
                    Trim(ws.Range("C" & i).Value) & ".pdf"
                .ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=strFileName, _
                    OpenAfterPublish:=False
                .Delete
            End With
        Next i
       
        Worksheets("master").Activate

    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 25, 2017 9:21 AM
  • Can you let me know on which module should I modify.
    Monday, December 25, 2017 11:18 AM
  • The module Mod1_main.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 25, 2017 11:50 AM
  • Thanx a lot.You have indeed helped me in saving a lot of time.

    Lastly,Can you help me with a separate module with vba code with 4th command button getting the same result in a single word document in different sheet in a document(doc or docx) instead of pdf separately (which was received awesome result by you).The other code should be left intact as per mine preference,I will choose any option when required.

    Tuesday, December 26, 2017 5:14 AM
  • That is an entirely different question. Please start a new topic/thread and ask your question there.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 26, 2017 9:34 AM
  • Ok.Thanx for the suggestion.Can you create a separate module with command button for the result in a single pdf file rather than separately which you have done greatefully in above post.As I require for printing all at once rather than one by one as each output has creates a different benefits.

    Hoping a positive response.


    Tuesday, December 26, 2017 11:53 AM
  • Hans

    I have been reading through all of the threads relating to using a button to save as PDF in Excel, the first formula which saves as "export" is working but when I try to add the formula below to use cell G19 as the file name it isn't working. Well it is only working when I go into the VB and exit then it saves. BUt not when I click on the create PDF button I created. Your help be much appreciated.  Thanks you  have been very helpful already

    Sub SavePDF()
         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
             Filename:="G:\Standard\Non Conformance\NCR PDF\" & _
             Range("G19").Value & "_" & Format(Date, "mm_dd_yyyy") & ".pdf", _
             OpenAfterPublish:=False
            
     End Sub

    Thursday, March 1, 2018 1:38 AM
  • What is the value of cell G19?

    Do you get an error message when you try to run the code? If so, what does it say?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, March 1, 2018 10:13 AM
  • That works fine when you want to link a folder stored in your local drive.

    But what's the solution when you want to link a folder in the cloud? 

    For example, when you want to give access to this excel file to multiple people within your organization the command button's VBA must contain a file path that works for every user. Having a file path in the form of C:/XXX etc. works only locally!

    Any suggestions to resolve this? 

    Thank you!!

    Wednesday, April 25, 2018 2:09 PM
  • @EvanNklp: I cannot help you with that, sorry. I would suggest that you start a new thread to ask how to use VBA to save a file in the cloud.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 25, 2018 2:19 PM
  • Superb! It worked quite well and at first attempt 😊 MANY THANKS!!!

    Friday, June 29, 2018 10:46 AM
  • You're welcome! Glad it was helpful.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 29, 2018 2:44 PM
  • Hey Mr. Vogelaar,  I am an instructor in a military formal school in which we do a lot of testing. When a student scores a 79 or below I have to write a counseling sheet for each individual. I've read this entire forum and though it helped me a lot I still need help with the following:

    I need "sheet2" (Counseling sheet) to pull info from "sheet1" (Test scores sheet) if a score of 79 or below is entered, and subsequently saving a PDF for every time that score range is recorded. 

    on the score sheet from B4 to B73 are the last names, C4 to c73 first names, and lastly E4 to E73 are the scores.

    Thank you in advance for the help!

     

    Wednesday, July 4, 2018 10:53 AM
  • @Sgt Reyes: Where should the last name and first name (and possibly the score) be placed in the counseling sheet?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 4, 2018 11:46 AM
  • Thank you for the prompt response good Sir,  the last me will go to "B5" on the counseling sheet; first name "AJ15", and score "BE12"

    Thanks again!

    Wednesday, July 4, 2018 4:39 PM
  • See if you can adapt this for your needs:

    Sub PDF()
        Dim wsA As Worksheet
        Dim wbA As Workbook
        Dim wsb As Worksheet
        Dim strName As String
        Dim strPath As String
        Dim strFile As String
        Dim strPathFile As String
        Dim r As Long
       
        On Error GoTo ErrHandler
       
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
       
        Set wbA = ActiveWorkbook
        'change this to the worksheet you want to PDF
        Set wsA = wbA.Worksheets("Sheet2")
        'change this to the worksheet containing the list of names and scores
        Set wsb = wbA.Worksheets("Sheet1")
       
        ' Target folder; path must end in a backslash
        strPath = "C:\PDF\"
       
        For r = 4 To 73
            ' Check score
            If wsb.Range("E" & r).Value <= 79 Then
                ' Last name
                wsA.Range("B5").Value = wsA.Range("B" & r).Value
                ' First name
                wsA.Range("AJ15").Value = wsA.Range("C" & r).Value
                'Score
                wsA.Range("BE12").Value = wsA.Range("E" & r).Value
                ' File name = lastname_firstname; you can modify this of course
                strName = wsb.Range("B" & r).Value & "_" & wsb.Range("C" & r).Value
                ' File name with extension
                strFile = strName & ".pdf"
                ' Full path
                strPathFile = strPath & strFile
                ' Save as PDF
                wsA.ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    Filename:=strPathFile, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=False
            End If
        Next r
       
    ExitHandler:
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
        Exit Sub
       
    ErrHandler:
        MsgBox "Could not create PDF file"
        Resume ExitHandler
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 4, 2018 7:48 PM
  • Thank you so much Mr. Vogelaar, you are indeed a Most Valuable Professional. The above code worked out exactly how I wanted it to.  

    Respectfully sent,  Oliver Reyes

    Thursday, July 5, 2018 2:33 AM
  • Good morning Mr. Vogelaar,

    I have an additional inquiry about this matter, is there a way that instead of creating individual PDFs for each failing score, all go to a single PDF?  

    Thank you again for all you do!!!

    Sunday, July 8, 2018 7:56 AM
  • What should the structure of that PDF file be?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 8, 2018 11:49 AM
  • Hans,    I hope I interpreted your question right... 

    Should be named "Counseling Sheets" and go straight to the desktop. Below is the code you provided me which I am currently using.

    Sub PDF()
    
    letter.Protect Password:="oli", UserInterfaceOnly:=True, AllowFiltering:=True
    
    ' PrintAPLetters
    
         Dim wsA As Worksheet
         Dim wbA As Workbook
         Dim wsb As Worksheet
         Dim strName As String
         Dim strPath As String
         Dim strFile As String
         Dim strPathFile As String
         Dim r As Long
             
         On Error GoTo ErrHandler
         
         Application.Cursor = xlWait
         Application.ScreenUpdating = False
         
         Set wbA = ActiveWorkbook
         'change this to the worksheet you want to PDF
         Set wsA = wbA.Worksheets("letter")
         'change this to the worksheet containing the list of names and scores
         Set wsb = wbA.Worksheets("scores")
         
         ' Target folder; path must end in a backslash
         strPath = Environ("USERPROFILE") & "\Desktop\"
    
         
         For r = 4 To 73
             ' Check score
             If wsb.Range("F" & r).Value <= 79 Then
                 ' Rank
                 wsA.Range("AI5").Value = wsb.Range("B" & r).Value
                 ' Last, First name
                 wsA.Range("A5").Value = wsb.Range("C" & r).Value
                 ' EDIPI
                 wsA.Range("BC5").Value = wsb.Range("D" & r).Value
                 'Score
                 wsA.Range("BD12").Value = wsb.Range("F" & r).Value
                 ' File name = lastname_firstname; you can modify this of course
                 strName = wsb.Range("B" & r).Value & "_" & wsb.Range("C" & r).Value
                 ' File name with extension
                 strFile = strName & ".pdf"
                 ' Full path
                 strPathFile = strPath & strFile
                 ' Save as PDF
                 wsA.ExportAsFixedFormat _
                     Type:=xlTypePDF, _
                     Filename:=strPathFile, _
                     Quality:=xlQualityStandard, _
                     IncludeDocProperties:=True, _
                     IgnorePrintAreas:=False, _
                     OpenAfterPublish:=True
             End If
         Next r
         
    ExitHandler:
         Application.ScreenUpdating = True
         Application.Cursor = xlDefault
         Exit Sub
         
    ErrHandler:
         MsgBox "Could not create PDF file"
         Resume ExitHandler
     End Sub
    


    Sunday, July 8, 2018 12:00 PM
  • OK, but currently the data on the letter sheet is being overwritten with the name and score in the loop.

    If you want all data to be on one sheet, we cannot overwrite the data. So please explain how you want the sheet to be laid out.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 8, 2018 12:53 PM
  • Mr. V,    I am confused on what you're asking.  All I would like, is that instead of each name becoming a single file that they're all in a massive pdf file. So if i have five students fail then i'll have one file with five letters vice five individual documents. Look at the link below.

    Thank you as always!

    https://drive.google.com/open?id=1TC5hDDB_wD_39PvZgTuyKoYarqvGegYw

    Sunday, July 8, 2018 8:13 PM
  • Thank you, seeing the workbook gave me a clear idea of what you want.

    Here is a modified macro:

    Sub PDF()

    letter.Protect Password:="oli", UserInterfaceOnly:=True, AllowFiltering:=True

    ' PrintAPLetters

        Dim wsA As Worksheet
        Dim wbA As Workbook
        Dim wsB As Worksheet
        Dim wbN As Workbook
        Dim wsN As Worksheet
        Dim strName As String
        Dim strPath As String
        Dim strFile As String
        Dim strPathFile As String
        Dim r As Long
           
       ' On Error GoTo ErrHandler
       
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
       
        Set wbA = ActiveWorkbook
        'change this to the worksheet you want to PDF
        Set wsA = wbA.Worksheets("letter")
        'change this to the worksheet containing the list of names and scores
        Set wsB = wbA.Worksheets("scores")
       
        ' New workbook, to be converted to PDF
        Set wbN = Workbooks.Add(xlWBATWorksheet)
       
        ' Target folder; path must end in a backslash
        strPath = Environ("USERPROFILE") & "\Desktop\"
       
        For r = 4 To 73
            ' Check score
            If wsB.Range("F" & r).Value <= 79 Then
                ' Copy letter sheet
                wsA.Copy After:=wbN.Worksheets(wbN.Worksheets.Count)
                Set wsN = wbN.Worksheets(wbN.Worksheets.Count)
                ' Rank
                wsN.Range("AI5").Value = wsB.Range("B" & r).Value
                ' Last, First name
                wsN.Range("A5").Value = wsB.Range("C" & r).Value
                ' EDIPI
                wsN.Range("BC5").Value = wsB.Range("D" & r).Value
                'Score
                wsN.Range("BD12").Value = wsB.Range("F" & r).Value
                ' Sheet name = lastname_firstname; you can modify this of course
                wsN.Name = wsB.Range("B" & r).Value & "_" & wsB.Range("C" & r).Value
            End If
        Next r
       
        ' Remove the blank first sheet
        wbN.Worksheets(1).Delete
        ' File name
        strName = "Counseling Sheets"
        ' File name with extension
        strFile = strName & ".pdf"
        ' Full path
        strPathFile = strPath & strFile
        ' Select all sheets
        wbN.Sheets.Select
        ' Save new workbook as PDF
        wbN.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strPathFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
        ' Optional: close new workbook
        wbN.Close SaveChanges:=False
       
    ExitHandler:
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
        Exit Sub
       
    ErrHandler:
        MsgBox "Could not create PDF file"
        Resume ExitHandler
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 8, 2018 8:48 PM
  • Thank you Mr. V, 

     Last thing, i notice that if i leave the score cell blank it generates a letter as well, how can i prevent blanks from generating?

    Respectfully sent, Oliver Reyes

    Sunday, July 8, 2018 9:12 PM
  • Change the line

            If wsB.Range("F" & r).Value <= 79 Then

    to

            If wsB.Range("F" & r).Value <= 79 And wsB.Range("F" & r).Value <> "" Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 8, 2018 9:16 PM
  • Muchas Gracias Mr. V, 

    I know you've heard this before but i'll say it again, you're the best!!

    You just helped me create a tool that will save us a lot of time. Thanks again and may God bless you and yours!!

    Respectfully sent, Oliver Reyes

    Sunday, July 8, 2018 9:35 PM
  • You're welcome! Glad to have been able to help.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 8, 2018 9:49 PM
  • Hello Hans,

    Thank you very much for helping us out! I've read through all the coding and found this one to be the closest to what I need. However, I'd greatly appreciate it if you could help me build in one more step to this module.

    Before the email is generated in Outlook, can I first apply an existing Action Wizard in Adobe? This step is to password protect the PDF.

    Thank you and I hope to hear from you.

    Sincerely,

    W

    Wednesday, August 15, 2018 5:20 AM
  • The code in this thread uses Excel's built-in "Save as PDF" feature; it doesn't use Adobe Acrobat. I suggest that you start a new thread here or in the Adobe forums.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 15, 2018 7:01 AM
  • Dear Hans,

    You help here has been really useful to me so far, but i have been struggling with the problem,

    as the given code for creating a PDF file and preparing to send it via Outlook, will not include my default signature.

    I am trying to prepare the same macro, only it should include my (and my colleagues' ones also, once they will use it) default singature in Outlook.

    Could you be able to help me change the given code to include the default signature?

    Thank you for the help and all the best,

    Otto


    Tuesday, August 21, 2018 12:53 PM
  • The code has a part like this:

        olMsg.Body = "Hello there!" & vbCrLf & _
           
    "Please see the attached PDF file." & vbCrLf & _
           
    "Cheers," & vbCrLf & _
           
    "(name of sender)"

    Change that to

        olMsg.HTMLBody = "Hello there!<br>" & _
           
    "Please see the attached PDF file.<br> & _
           
    "Cheers,<br> & _
           
    "(name of sender)"<br>" & _
            olMsg.HTMLBody


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 21, 2018 2:25 PM
  • Dear Hans,

    This is the code i am using right now (as below), but it still won't include my default signature.

    Could there be a problem due to the fact that i am using the company picture file in my signature?

    Sub SaveAndSendPDF()
        Dim strFile As String
        Dim olApp As Object
        Dim HTML As Object
        ' Modify path and filename as needed
        strFile = "\\TATRM01\TAKUSERS$\AY876131\My Documents\Test\" & "Available capacity report" & " " & Format(Now(), "dd.mm.yy") & ".pdf"
        ' Export to PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFile, OpenAfterPublish:=False
        ' Start Outlook
        On Error Resume Next
        Set olApp = GetObject(Class:="Outlook.Application")
        If olApp Is Nothing Then
            Set olApp = CreateObject(Class:="Outlook.Application")
            If olApp Is Nothing Then
                MsgBox "Cannot start Outlook", vbExclamation
                Exit Sub
            End If
        End If
        On Error GoTo ErrHandler
        olApp.Session.Logon
        ' Create new mail message
        Set olMsg = olApp.CreateItem(0) ' 0 = olMailItem
        ' Specify recipient
        olMsg.To = "you@somewhere.com"
        ' Specify subject
        olMsg.Subject = "Available capacity +7 days"
        ' Specify body text
        olMsg.HTMLBody = "Dear all,<br>" & _
            "<br>" & _
            "Please see attached the available capacity report +7 days.<br>" & _
            "<br>" & _
            "Please note that data is taken from our data warehouse early morning, so slight differences can occur.<br>" & _
            "<br>" & _
            "Instructions for the report:<br>" & _
            "<br>" & _
            "Cheers,<br>" & _
            "<br>" & _
            "(name of sender)<br>" & _
         olMsg.HTMLBody
        ' Attach file
        olMsg.Attachments.Add strFile
        ' Display the message
        ' If you want to send immediately, replace Display with Send
        olMsg.Display
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
    End Sub
    

    Wednesday, August 22, 2018 12:42 PM
  • I'm sorry, I found many posts that state that something like that code should work. It doesn't, though, at least not for me in Outlook 2016.

    I'm out of my depth here. It'd be better if an Outlook expert looked at this, so I suggest that you ask how to insert the default signature in a new thread in the Outlook for Developers forum.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 22, 2018 6:06 PM
  • Hi Hans,

    Your responses here have been great help to me and many people.

    I couldnt really find any solution yet to above quoted question yet. Could you help with the code for such requirement?

    Thanks in advance!

    Priya

    • Edited by Priya_007 Wednesday, October 24, 2018 9:10 AM
    Wednesday, October 24, 2018 9:07 AM
  • Do you mean something like this:

    Sub SavePDFs()
        Dim i As Long
        For i = 1 To 5000
            Range("A1").Value = i
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:="C:\PDF\Report_" & i & ".pdf", _
                OpenAfterPublish:=False
        Next i
    End Sub

    Replace C:\PDF with the path of the folder in which you want to save the PDF files.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 24, 2018 9:21 PM
  • @Maddie Rice:

    Try the following code. Change "Sheet 1" and "Sheet 2" to the real names of the worksheets, and change "C:\PDF\" to the path of the folder in which the PDF files should be saved.

    The code uses the client name as file name.

    Sub SavePDFs()
        Dim cel As Range
        For Each cel In Worksheets("Sheet 1").Range("D2:D124")
            Worksheets("Sheet 1").Range("C1").Value = cel.Value
            Worksheets("Sheet 1").ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:="C:\PDF\" & cel.Value & ".pdf", _
                OpenAfterPublish:=False
        Next cel
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 26, 2018 11:00 PM
  • Hi,

    I have a column containing 254 cells and I'd like to export each cell to a separate PDF, with the PDF name for each cell generated by a count of 1-254. I've looked through this thread and haven't seen anything similar to that. 

    I'd really appreciate some help!

    Thanks

    Saturday, March 2, 2019 11:44 AM
  • Let's say the cells are A2:A255:

    Sub ExportCells2PDF()
        Dim r As Long
        Dim strPath As String
        Dim strFile As String
        ' Set the folder path
        strPath = ThisWorkbook.Path & "\"
        ' Loop through the cells
        For r = 2 To 255
            If Range("A" & r).Value <> "" Then
                strFile = r - 1 & ".pdf"
                Range("A" & r).ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    Filename:=strPath & strFile
            End If
        Next r
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 2, 2019 4:31 PM
  • I'm using the code below but want to modify so I can pdf and save several worksheets in same workbook. 

    For example, I have four worksheets:

    Reference (where macro button is), Revenue, Profit, and Cash. 

    1. Is there a way to have a single button that will allow me to pick the location to Save As and save all three worksheets as a pdf separately?  i.e Revenue.pdf, Profit.pdf, and Cash.pdf

    2. is there a way to have the Macro pdf and save just one worksheet but have that worksheet be named in a specific cell?  For Example, replace [ ActiveWorksheet.ExportAsFixedFormat Type:=xlTypePDF, _] with something like [ Worksheet("C19").ExportAsFixedFormat Type:=xlTypePDF, _]  I realize my syntax is off in the second one.

    This is what I'm currently using to save the Active Worksheet 

     Sub SavePDF()
        Dim strFileName As String
        strFileName = Application.GetSaveAsFilename( _
            FileFilter:="PDF Files (*.pdf),*.pdf", _
            Title:="Save As PDF")
        If strFileName <> "False" Then
            ActiveWorksheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strFileName, _
                OpenAfterPublish:=True
        End If
    End Sub

    Thank you in advance.  You work on this message board is very helpful. 

    Monday, April 15, 2019 7:19 PM
  • 1. To save three worksheets as separate PDF files:

    Sub Save3Sheets()
        Dim strFolder As String
        Dim varSheet As Variant
        With Application.FileDialog(4) ' msoFileDialogFolderPicker
            If .Show Then
                strFolder = .SelectedItems(1) & "\"
            Else
                Beep
                Exit Sub
            End If
        End With
        For Each varSheet In Array("Revenue", "Profit", "Cash")
            Worksheets(varSheet).ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & varSheet & ".pdf", _
                OpenAfterPublish:=False
        Next varSheet
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 15, 2019 8:00 PM
  • 2. To save the active sheet, using the value of cell C19 on that sheet as filename:

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=Range("C19").Value & ".pdf", _
        OpenAfterPublish:=False
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 15, 2019 8:03 PM
  • 1. To save three worksheets as separate PDF files:

    Sub Save3Sheets()
        Dim strFolder As String
        Dim varSheet As Variant
        With Application.FileDialog(4) ' msoFileDialogFolderPicker
            If .Show Then
                strFolder = .SelectedItems(1) & "\"
            Else
                Beep
                Exit Sub
            End If
        End With
        For Each varSheet In Array("Revenue", "Profit", "Cash")
            Worksheets(varSheet).ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & varSheet & ".pdf", _
                OpenAfterPublish:=False
        Next varSheet
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thank you.  Unfortunately I get an error message that says: Cannot run the macro "[insert my file and macro name].  The macro may not be available iin this workout or all macros may be disabled.

    When I copied the old macro in it worked fine so not sure what may be disabled on mine.   

    Monday, April 15, 2019 8:48 PM
  • Make sure that you copy the macro into a standard module, that is the type that you create by selecting Insert > Module in the Visual Basic Editor.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 15, 2019 8:52 PM
  • Make sure that you copy the macro into a standard module, that is the type that you create by selecting Insert > Module in the Visual Basic Editor.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    That worked. I had previously just pasted it into the old Module but opening a new module worked. Not really sure why, but it worked. Thank you.
    Monday, April 15, 2019 8:59 PM
  • 1. To save three worksheets as separate PDF files:

    Sub Save3Sheets()
        Dim strFolder As String
        Dim varSheet As Variant
        With Application.FileDialog(4) ' msoFileDialogFolderPicker
            If .Show Then
                strFolder = .SelectedItems(1) & "\"
            Else
                Beep
                Exit Sub
            End If
        End With
        For Each varSheet In Array("Revenue", "Profit", "Cash")
            Worksheets(varSheet).ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & varSheet & ".pdf", _
                OpenAfterPublish:=False
        Next varSheet
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    This worked well, and looking for some additional functionality.  Is there a way so to only have certain specific sheets print based on whether they are needed?  For example, I may need only Profit, or only two of the three.  Trying to see if I can created a toggle where if checked which creates a cell reference of TRUE the sheet saves in pdf and if not checked (i.e. FALSE), it is not saved. 
    Monday, April 15, 2019 9:11 PM
  • You can insert check boxes from the Form Controls section of Insert Controls in the Controls group of the Developer tab of the ribbon. Right-click a check box and select Format Control... to set the Cell link, i.e. the cell that will contain TRUE/FALSE depending on the state of the check box.

    Let's say the check boxes for Revenue, Profit and Cash are linked to cells C2, C3 and C4, respectively.

    Sub Save3Sheets()
        Dim strFolder As String
        Dim varSheet As Variant
        With Application.FileDialog(4) ' msoFileDialogFolderPicker
            If .Show Then
                strFolder = .SelectedItems(1) & "\"
            Else
                Beep
                Exit Sub
            End If
        End With
        If Range("C2").Value = True Then
            Worksheets("Revenue").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & "Revenue.pdf", _
                OpenAfterPublish:=False
        End If
        If Range("C2").Value = True Then
            Worksheets("Profit").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & "Profit.pdf", _
                OpenAfterPublish:=False
        End If
        If Range("C2").Value = True Then
            Worksheets("Cash").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & "Cash.pdf", _
                OpenAfterPublish:=False
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Monday, April 15, 2019 9:23 PM
  • You can insert check boxes from the Form Controls section of Insert Controls in the Controls group of the Developer tab of the ribbon. Right-click a check box and select Format Control... to set the Cell link, i.e. the cell that will contain TRUE/FALSE depending on the state of the check box.

    Let's say the check boxes for Revenue, Profit and Cash are linked to cells C2, C3 and C4, respectively.

    Sub Save3Sheets()
        Dim strFolder As String
        Dim varSheet As Variant
        With Application.FileDialog(4) ' msoFileDialogFolderPicker
            If .Show Then
                strFolder = .SelectedItems(1) & "\"
            Else
                Beep
                Exit Sub
            End If
        End With
        If Range("C2").Value = True Then
            Worksheets("Revenue").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & "Revenue.pdf", _
                OpenAfterPublish:=False
        End If
        If Range("C2").Value = True Then
            Worksheets("Profit").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & "Profit.pdf", _
                OpenAfterPublish:=False
        End If
        If Range("C2").Value = True Then
            Worksheets("Cash").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFolder & "Cash.pdf", _
                OpenAfterPublish:=False
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thank you.  This worked well.  You are a valuable resource.  Is there a way to make the type larger on the "Check Box" form controls? 
    Monday, April 15, 2019 9:58 PM
  • To increase the text size of a Form Controls check box, you'd need to increase the zoom percentage of the worksheet.

    Alternatively, you could use check boxes from the ActiveX Controls section. To edit such a check box, turn on Design Mode in the Controls group of the Developer tab of the ribbon. Then right-click the check box and select Properties from the context menu.

    Click in the Font property, then click the … button to change the font size.

    Click in the LinkedCell property to type the address of the linked cell.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 15, 2019 10:13 PM
  • One more quick question, is there a way to name the files by adding "Cash" and then a reference to a cell that has a specific name in it? So for the code line [Filename:=strFolder & "Cash.pdf", _] it would be something like

    Filename:=strFolder & "Cash" &"C19".pdf", _ which would have the result "Cash ABC Inc.pdf"

    This assumes "ABC Inc" was in cell C19

     
    Monday, April 15, 2019 10:19 PM
  • Use

    Filename:=strFolder & "Cash " & Range("C19").Value & ".pdf"

    Note the space after Cash within the quotes.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 15, 2019 10:29 PM
  • Hans,
    everything you have helped me with so far has worked perfectly.  I've hit a snag on the following.  Everything below was working fine but now it fails on the underlined/italicized area and I don't know what I'm
    missing.  The error message is "Run-time error '1004': Document not saved.  The document may be open, or an error may have been in encountered when saving."    I've tried this a few times and made sure that the document was not open and I've tried searching my computer to see if its saved somewhere else on the computer; However, if I change the name of it then it works until the next series of commands and fails again with the same error message.  Any help is much appreciated. 

    <o:p> </o:p>Sub SaveTheSheets()
         Dim strFolder As String
         Dim varSheet As Variant
         With Application.FileDialog(4) ' msoFileDialogFolderPicker
             If .Show Then
                 strFolder = .SelectedItems(1) & "\"
             Else
                 Beep
                 Exit Sub
             End If
         End With
         If Range("c21").Value = True Then
             Worksheets("IDAM").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "IDAM_" & _
                 ActiveSheet.Range("d8").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If
         If Range("c22").Value = True Then
             Worksheets("IDAS").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "IDAS_" & _
                 ActiveSheet.Range("d8").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If
         If Range("c23").Value = True Then
             Worksheets("EC").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "EC_" & _
                 ActiveSheet.Range("d8").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If
         If Range("c24").Value = True Then
             Worksheets("ECCo#1").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "EC_" & _
                 ActiveSheet.Range("g8").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If
         If Range("c25").Value = True Then
             Worksheets("ECCo#2").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "EC_" & _
                 ActiveSheet.Range("j8").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If
         If Range("c26").Value = True Then
             Worksheets("ECP_IP").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "ECP_" & _
                 ActiveSheet.Range("l8").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If
         If Range("c27").Value = True Then
             Worksheets("ECP_G").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "ECP_" & _
                 ActiveSheet.Range("l6").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If

    Thursday, April 25, 2019 11:37 PM
  • What happens if you change all the occurrences of

    OpenAfterPublish:=True

    to

    OpenAfterPublish:=False

    (you can do this with Edit > Replace...)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 26, 2019 8:36 AM
  • Making the OpenAfterPublish:=False did allow them all to be saved without an issue.

    I played around with things a little in how I was "naming" the sheet by changing what was in cell G8 and that also fixed the problem but it ran to the next set of code and came up with

    "Run-time error: 2147018887 (80071779)" Document not saved. 

    One workaround I found was adding "On Error Resume Next" after the first line at the beginning of the code and that caused it to run all the way through but I feel like I'm not really fixing the problem. 

    Thank you. 

    Friday, April 26, 2019 11:42 AM
  • I suspect it's a timing problem - using OpenAfterPublish:=True causes 7 PDF files to be opened in quick succession; your PDF application may not be able to cope with that.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 26, 2019 1:38 PM
  • Thank you, that makes sense. Is there a way to program in a slight delay in VBA before it tries to save and open each one?
    Friday, April 26, 2019 7:07 PM
  • First, see if it helps if you insert one or two lines

        DoEvents

    after each ExportAsFixedFormat instruction


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 26, 2019 7:12 PM
  • I inserted DoEvents after each ExportAsFixedFormat instruction but got an error.  (see below but I did it w)

      If Range("c25").Value = True Then
             Worksheets("EC").ExportAsFixedFormat _
                DoEvents
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "EC_" & _
                 ActiveSheet.Range("j8").Value & ".pdf", _
                 OpenAfterPublish:=True
         End If

    So I tried:

    If Range("c25").Value = True Then
             Worksheets("EC").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "EC_" & _
                 ActiveSheet.Range("l8").Value & ".pdf", _
                 OpenAfterPublish:=True
                 DoEvents
                 DoEvents
         End If

    While the second one ran, it still got hung up on the same issue. 

    Friday, April 26, 2019 7:45 PM
  • All the lines ending with _ form a single instruction with the following line. So

             Worksheets("EC").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "EC_" & _
                 ActiveSheet.Range("j8").Value & ".pdf", _
                 OpenAfterPublish:=True

    is one instruction. The DoEvents line should go below that:

             Worksheets("EC").ExportAsFixedFormat _
                 Type:=xlTypePDF, _
                 Filename:=strFolder & "EC_" & _
                 ActiveSheet.Range("j8").Value & ".pdf", _
                 OpenAfterPublish:=True
            DoEvents


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 26, 2019 7:53 PM