VB code to save as PDF with a changeable file name

[Col Werner]

Hi,


I've written a macro to send an excel sheet to save as a PDF. I've got a specific (changable) name that I'd like it to save as but I can't quite work out what command to give it so that it pastes the name (which I've copied in excel in the macro - called "Filename_Paste") as the new file name.


For example my PDF saves onto my desktop as Filename_Paste.PDF whereas I'd like to save as "Simon and Betty Anderson 3-6-2020" which is what I've just copied in the macro using Filename_Paste as a named range.


I'm also trying to work out how would I get the file to save on the users desktop, at the moment it works for my desktop but this will not work when I hand it to one of my colleagues machines.

Code is as follows in Bold:


Sub Macro4()

'
' Macro4 Macro
'
'
Range("Filename_Copy").Select
Selection.Copy
Range("Filename_Paste").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Filename_Paste").Select
Application.CutCopyMode = False

Selection.Copy
Application.CutCopyMode = False
ChDir "C:\Users\jb\Desktop\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\jb\Desktop\" & "Filename_Paste", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Range("A1").Select
End Sub







Any help would be appreciated!


Col






Col

Continue reading...