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

[salmonchild]

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

 

[Luke M]

Try this:

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

 

[salmonchild]

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!

 

[Luke M]

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:

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

 

[Oxidised]

This worked for me as well, thanks!

 

[salmonchild]

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.

 

[Deepak]

This might be...

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

 

[salmonchild]

Many thanks Deepak!

 

[Deepak]

Welcome!!

 

[Luc]

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:

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

 

[blairski10]

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?

 

[tim220225]

Luke I know this is an old post that I came across. I used your code for my project and it worked great. How can I amend the code to check for a duplicate file in the save folder and prompt me to overwrite the file or to change the name? Should the file not exist the code just needs to continue running.

Thanks in advance.

Tim

Try this:

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

 

[vletm]

tim220225
As a new member, You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Start a new post every time you ask a question, even if the theme is similar.