PDA

View Full Version : Name & Save workbook from a specific sheet cell value



steelstorm
10-17-2013, 10:05 AM
Hi All,

I have the below code working fine, but I require a slight modification that I cannot seem to get right.

The workbook has multiple sheets, what I would like to do is use the value located on worksheet "SUMMARY REPORT" cell "B3" to be the name the file is saved as. I've read a few articles on how to name from a cell, but I'm not having any luck naming it from a cell of a specific sheet. My VB skills are still greatly lacking so any help is much appreciated.

Sub PDFTEST()
'
' PDFTEST Macro
'
'
ChDir "C:\Users\phogel\Desktop"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\phogel\Desktop\test.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
'change true to false on line above if I do not want it to open pdf after converting
End Sub

p45cal
10-17-2013, 01:24 PM
Sub PDFTEST()
FName = "C:\Users\phogel\Desktop\" & Sheets("SUMMARY REPORT").range("B3") & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'change true to false on line above if I do not want it to open pdf after converting
End Sub

steelstorm
10-17-2013, 01:39 PM
Sub PDFTEST()
FName = "C:\Users\phogel\Desktop\" & Sheets("SUMMARY REPORT").range("B3") & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'change true to false on line above if I do not want it to open pdf after converting
End Sub



Ugh, I was so darn close.

Your reply worked perfectly. Thanks alot!