VBA to ask for a file location to be manually selected

[JPL_76]

I have a worksheet that is set out as a report - users pick a customer from a drop down list and the rest of the report is populated based on cell contents on many other worksheets.

I am trying to export these cells to a .pdf file so that they can be shared with stakeholders and I've got the vba working when the workbook 'lives' in my C: drive, but its a team tool, and therefore, 'lives' on Microsoft Teams. I'm using the VBA:

Sub SaveRangeAsPDF()

'Create and assign variables
Dim ws As Worksheet
Dim rng As Range

Set ws = Sheets("Sheet4")
Set rng = ws.Range("b2:ai38")

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Range("b2") & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"

End Sub

and when the workbook is on my C drive, when I run the report, it creates a .pdf with the filename of the customer and a timestamp in the same location that the workbook resides.

When I upload it to Teams, I get a 400 error as I assume, it doesn't know where to save the file.

What can I add to this VBA so that it brings up the SaveAs box, ideally pre-populated with the filename and .pdf extension. I've tried using

Application.getsaveasfilename InitialFilename:=Range("b2") & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"

but not having any luck at all. Any help would be gratefully received.

Thanks in advance,

[#2 · Accepted solution]

@JPL_76 

The Application.GetSaveAsFilename method is intended to return a value, but your syntax cannot receive a value. It makes me wonder what you are expecting the method to do.

Use syntax that captures a value, such as:

    Dim vntFileSpec As Variant
    '
    vntFileSpec = Application.GetSaveAsFilename(InitialFileName:=Range("b2") _
            & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf")
    If vntFileSpec = False Then
        '...the user decided not to save.  Exit the procedure or do
        '    whatever else is appropriate.
    Else
        'Use vntFileSpec in your <workbook>.SaveAs or <worksheet>.SaveAs method
        'or <range>.ExportAsFixedFormat method or whatever.
    End If

[#3]

Thanks - I'm an absolute newbie to VBA and I've used Google to help me with the VBA I've referenced above, as you would expect there are a number of one-size-fits-all options but nothing specific for what I want/ need. As a result of being a noob, I'm not quite sure what to do with the VBA you've provided, does that replace my script altogether, or do I embed it within what I've done?