Recorded Macro wont paste a cell and save file name

[Adski989]

Hi all,

as above, when i record the macro I copy a specific cell to be the file name, e.g customer name to enable each time i run the macro the excel sheet to be saved as a different named PDF, unfortunately the macro just takes the first file saved name and stores it to be used each time I run the macro... any pointers please?

Sub Macro5()
'
' Macro5 Macro
'


'
Range("B6").Select
Selection.Copy
ChDir "C:\Users\adam.galilee\Desktop\Quotations"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Desktop\Quotations\Bob 1.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Application.CutCopyMode = False
Range("I1").Select
End Sub

 

[wideboydixon]

Assuming B6 contains, for example, "Bob 1.pdf" then it's simply something like this:

Sub Macro5()

Dim saveFileName As String

saveFileName = Environ("USERPROFILE") & "\Desktop\Quotations\" & Range("B6").Value
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

WBD

 

[Adski989]

SUPER STAR!! works perfectly, thanks very much