1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to save page as .pdf, name to come from cell values

Discussion in 'VBA Macros' started by salmonchild, Sep 3, 2013.

  1. salmonchild

    salmonchild Member

    Messages:
    31
    Excel warriors,

    A cheeky request for a bit of scripting- could anyone please provide me with VBA code to save the current active sheet as a .pdf in a defined network drive, and for the name of the file to be a combination of the text in cells A1 A2 and A3?

    I have managed through recording a macro and then tinkering with it to achieve the first two but naming the .pdf I just cannot work out.

    If you could help you would be a superstar!



    salmon
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,266
    Try this:
    Code (vb):

    Sub SaveAsPDF()
    'Saves active worksheet as pdf using concatenation
    'of A1,A2,A3

    Dim fName As String
    With ActiveSheet
        fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\My Documents\" & fName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
    End Sub
     
    Ganesh Kanade and salmonchild like this.
  3. salmonchild

    salmonchild Member

    Messages:
    31
    Cheers Luke, if I know the range of cells that from the page, e.g. A1:K30 is there another line I can put in to make that the 'active sheet' for the save.

    It is always awesome to see how clean the VBA can be if you know what you are doing- I usually search for the different components or record bits and try to stitch them together. Mostly it works but it looks like Frankenstein's monster!
  4. Luke M

    Luke M Excel Ninja

    Messages:
    9,266
    Not sure I understand what you mean...do you now want to use the active sheet for the pdf, or you want the cells that generate the name to come from a different spot? if the latter:
    Code (vb):

    Sub SaveAsPDF()
     
     
    Dim fName As String
    With Worksheets("My Sheet")
        fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value
    End With
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\My Documents\" & fName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
     
    End Sub
    salmonchild likes this.
  5. Oxidised

    Oxidised Member

    Messages:
    61
    This worked for me as well, thanks!
  6. salmonchild

    salmonchild Member

    Messages:
    31
    This has been working brilliantly for over a year - props to Luke for the code.

    My requirements have changed a little now and instead of a .pdf of the active sheet I would like it to save as a workbook in .xlsx format- is there a tweak to this code that can change the file type? I did some searching and THIS details making a copy of the sheet and saving as a new workbook but I can't reconcile the two to keep the file name generation from concatenating A1, A2 and A3.

    Help gratefully received.
  7. Deepak

    Deepak Excel Ninja

    Messages:
    2,731
    This might be...

    Code (vb):

    Option Explicit

    Sub SaveAsWB()
    Dim fName As String
    With Worksheets("My Sheet")
        fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value

    .Copy
        ActiveWorkbook.SaveAs "C:\My Documents\" & fName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close

    End With

    End Sub

     
    Luke M and salmonchild like this.
  8. salmonchild

    salmonchild Member

    Messages:
    31
    Many thanks Deepak!
  9. Deepak

    Deepak Excel Ninja

    Messages:
    2,731
    Welcome!!
  10. Luc

    Luc New Member

    Messages:
    1
    Hi Luke,

    Thanks for your advice. I have tried to implement, but unsuccessfully. Trying to save my worksheet to PDF using cell F5 value as filename. I keep getting the "Compile error: Syntax error" message. I am working with Excel 2013, and using the following code:

    Code (vb):
    Sub Save_PDF()
    'Saves active worksheet as pdf using rep name

    Dim fName As String
    With ActiveSheet
        fName = .Range("F5").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:/Users/LANGELU3/Desktop/M1_2016_Payout_Sheets/" & fName,_
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
    End Sub
    Can you please point me to where the error is?
    Thanks, Luc
  11. blairski10

    blairski10 New Member

    Messages:
    3
    What if I wanted to run this Save to PDF on a loop? I have a dashboard that needs to populate and then save to a PDF for about 700 clients. Using a key cell (Let's say it's A1 on a different sheet than the dashboard), how would I incorporate this code into what Luke has already come up with?

Share This Page