Saving Print Ranges

[omagoodness]

I have a named print range on a sheet ("TipsPaid") that I want to save to pdf using VBA and command button. The code I am using (below) will select the range and open in a pdf document but does not save it to a file. I have been over 4 hours trying to figue out why it will not save. What am I doing wrong? I'm sure it is something simple but I don't see it. Please help.

Option Explicit
Private Sub cmdSave_Click()
'declaring variables

'Setting range to be saved
Dim PRng As Range
Set PRng = Range("TipsPaid")

'path to save folder
Dim myPath As String
myPath = Application.ThisWorkbook.Path & "\" & "Tip-out Records"

'setting file name
Dim SDate As String, filename As String
SDate = Application.WorksheetFunction.Text(Cells(3, 17), "dd mmm yyyy")
filename = "Week Ending" & " " & SDate & ".pdf"

'Save Location
 Dim SaveLocation As String
 SaveLocation = myPath & " \ " & filename
'Save as pdf
PRng.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
             "SaveLocation", Quality:=xlQualityStandard, _
             IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


End Sub

 

[BSALV]

it's Savelocation without double quotes !!!

PRng.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
             SaveLocation, Quality:=xlQualitySt....

 

[omagoodness]

it's Savelocation without double quotes !!!

PRng.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
             SaveLocation, Quality:=xlQualitySt....

Thank you for your reply. When I removed the quotes, I got an error.

 

[BSALV]

SaveLocation = myPath & " \ " & filename WRONG
SaveLocation = myPath & "\" & filename GOOD
there are no spaces in that separator

 

[omagoodness]

SaveLocation = myPath & " \ " & filename WRONG
SaveLocation = myPath & "\" & filename GOOD
there are no spaces in that separator

Thanks. I'm still getting a Runtime 1004 message at the saving part. Below is my code with the 2 suggested corrections as well as a pic showing where the debug takes me. Man, I sure hate when this happens.

Option Explicit
Private Sub cmdSave_Click()
'declaring variables

'Setting range to be saved
Dim PRng As Range
Set PRng = Range("TipsPaid")

'path to save folder
Dim myPath As String
myPath = Application.ThisWorkbook.Path & "\" & "Tip-out Records"

'setting file name
Dim SDate As String, filename As String
SDate = Application.WorksheetFunction.Text(Cells(3, 17), "dd mmm yyyy")
filename = "Week Ending" & " " & SDate & ".pdf"

'Save Location
 Dim SaveLocation As String
 SaveLocation = myPath & "\" & filename
'Save as pdf
PRng.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
             SaveLocation, Quality:=xlQualityStandard, _
             IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


End Sub

 

[BSALV]

is there a "Tip-out Records" subdirectory under the actual path, thus the path thisworkbook.path & "\Tip-out Records", that exists ?
do you get an error if you add that 2nd line in your macro ? (is that "application" necessary ?)

myPath = Application.ThisWorkbook.Path & "\" & "Tip-out Records"
chdir mypath

 

[BSALV]

short version

Private Sub cmdSave_Click()
     Dim MyFilename As String

     MyFilename = ThisWorkbook.Path & "\" & "Tip-out Records\Week ending_" & Format(Cells(3, 17), "dd mmm yyyy hhmmss") & ".pdf" 'for test also with hours minutes seconds
     MsgBox MyFilename, vbInformation, UCase("Saving pdf")
     Range("TipsPaid").ExportAsFixedFormat xlTypePDF, MyFilename, OpenAfterPublish:=True

End Sub

 

[omagoodness]

short version

Private Sub cmdSave_Click()
     Dim MyFilename As String

     MyFilename = ThisWorkbook.Path & "\" & "Tip-out Records\Week ending_" & Format(Cells(3, 17), "dd mmm yyyy hhmmss") & ".pdf" 'for test also with hours minutes seconds
     MsgBox MyFilename, vbInformation, UCase("Saving pdf")
     Range("TipsPaid").ExportAsFixedFormat xlTypePDF, MyFilename, OpenAfterPublish:=True

End Sub

short version

Private Sub cmdSave_Click()
     Dim MyFilename As String

     MyFilename = ThisWorkbook.Path & "\" & "Tip-out Records\Week ending_" & Format(Cells(3, 17), "dd mmm yyyy hhmmss") & ".pdf" 'for test also with hours minutes seconds
     MsgBox MyFilename, vbInformation, UCase("Saving pdf")
     Range("TipsPaid").ExportAsFixedFormat xlTypePDF, MyFilename, OpenAfterPublish:=True

End Sub

Still getting the same error. Yes, the subfolder "Tip-out Record" does exist. If I add a message box with the SaveLocation, the path and file name is exactly as it should be. The problem seems to exist in the export to pdf line.

 

[BSALV]

does that named range exist ?

Sub test()
     Dim c, MyFilename
     MyFilename = ThisWorkbook.Path & "\" & "Tip-out Records\Week ending_" & Format(Cells(3, 17), "dd mmm yyyy hhmmss") & ".pdf"     'for test also with hours minutes seconds
     MsgBox MyFilename, vbInformation, UCase("Saving pdf")
     ChDir ThisWorkbook.Path & "\" & "Tip-out Records"     'test existence of this subdirectory
     Set c = Range("TipsPaid")     'teste existence of this named range
     Range("TipsPaid").ExportAsFixedFormat xlTypePDF, MyFilename, OpenAfterPublish:=True
End Sub

 

[omagoodness]

does that named range exist ?

Sub test()
     Dim c, MyFilename
     MyFilename = ThisWorkbook.Path & "\" & "Tip-out Records\Week ending_" & Format(Cells(3, 17), "dd mmm yyyy hhmmss") & ".pdf"     'for test also with hours minutes seconds
     MsgBox MyFilename, vbInformation, UCase("Saving pdf")
     ChDir ThisWorkbook.Path & "\" & "Tip-out Records"     'test existence of this subdirectory
     Set c = Range("TipsPaid")     'teste existence of this named range
     Range("TipsPaid").ExportAsFixedFormat xlTypePDF, MyFilename, OpenAfterPublish:=True
End Sub

Thank you. I stuck that in at the bottom of my code and it saved a .pdf in the right folder but it was totally blank. I guess I just need to tweak a bit to include the range. I will ge tback to you on what happens. I have to be away from my computer for a while but I will get back to you. Thanks again.