I have an excel file with VBA code that generates a summary report, then moves the summary report into a new book and then saves that report into a pre-determined folder. The code I use to do this is:
Code:
    'Save summary sheet to a new work book

    compile_date = Format(Now, "d mmm yy")
    name_of_file = "Report " & compile_date

    Dim w As Workbook, ws As Worksheet, ss As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "summary" Then
            If w Is Nothing Then
                ws.Move
                Set w = ActiveWorkbook
            Else
                ws.Move after:=ss
            End If
             Set Wb = ActiveWorkbook
            Set ss = ActiveSheet
        End If
    Next ws

    ThisWorkbook.Activate
        
Wb.SaveAs Filename:= _
      "G:\My Branch \Dragon Files\” & name_of_file & ".xls"
This works fine for me. (I’ve simplified the code a bit for illustration purposes: the complete version chooses the folder based upon the date.) The file path is in a group drive. I would like to send my report generator to someone else in my organisation. She belongs to a different branch and I don’t know how she’s set up the folders in her G drive. So I want to rewrite the code so that the MS Excel Save As dialogue box comes up (preferably with the name of the file in the File Name field), so that she can choose where she wants to save it.
To date, my attempts to do so have resulted in a Save As dialogue box that tries to save the file that generates the report and not the report itself.
So, how do I go about this?
Thank you