-
Jun 16th, 2020, 02:55 AM
#1
Thread Starter
New Member
VB code to save as PDF with a changeable file name
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:
Code:
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
Last edited by dday9; Jun 16th, 2020 at 08:35 AM.
-
Jun 16th, 2020, 11:54 AM
#2
Re: VB code to save as PDF with a changeable file name
Methods to get path to user desktop:
http://learnexcelmacro.com/wp/2012/1...h-excel-macro/
To change the name of the saved file, replace the Filename_Paste with the actual name that you want. Where is the "Simon and Betty Anderson 3-6-2020" information stored?
-
Jun 16th, 2020, 09:27 PM
#3
Thread Starter
New Member
Re: VB code to save as PDF with a changeable file name
Originally Posted by jdc2000
Thanks "Simon and Betty Anderson 3-6-2020" is stored in the named range called "Filename_Paste"
-
Jun 17th, 2020, 10:12 AM
#4
Re: VB code to save as PDF with a changeable file name
Where in the Range would find it? Is it in a cell, and, if so, how would you locate that cell?
Once you know where the data is located, you can change the code to use that data as the file name.
-
Jun 17th, 2020, 11:43 PM
#5
Thread Starter
New Member
Re: VB code to save as PDF with a changeable file name
Yes is in a cell, cell D43 and I named that cell as a named range "Filename_Paste". The actual data that sits in that cell is "Simon and Betty Anderson 3-6-2020" but that will change every time I hit the Macro.
I'm just confused on how to apply the code to my situation at hand..
-
Jun 18th, 2020, 10:27 AM
#6
Re: VB code to save as PDF with a changeable file name
Possibly useful example:
Code:
Sub Macro4()
'
' Macro4 Macro
'
'
Dim strFileName as String
strFileName = CStr(ActiveSheet.Cells(4, 43))
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" & strFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Range("A1").Select
End Sub
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|