[RESOLVED] Excel: Move worksheet to new book and save as using dialog box

[Resource Dragon]

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:

    '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

[Siddharth Rout]

Something like this?

Sub YourMacro()
    Dim fileSaveName As String

    '~~> Your other codeOTHER CODE
    
    fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="xls Files (*.xls), *.xls")

    If fileSaveName <> "False" Then
        ActiveWorkbook.SaveAs fileSaveName
    End If
End Sub

[Resource Dragon]

I F8-ed through it all. The problem is that the code I was using to move the sheet set the focus back to the report generator file, instead of going to the new file. With a "GoTo" inserted in the code, it is now doing what I want.

  Sub ReportSaver()
   
    Dim fileSaveName As String

    'Save summary sheet to a new work book

    compile_date = Format(Now, "d mmm yy")    '  name_of_file
 fileSaveName = "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
   GoTo SaveItAs
            Else
                ws.Move after:=ss

            End If
             Set Wb = ActiveWorkbook
            Set ss = ActiveSheet
        End If
    Next ws

    ThisWorkbook.Activate


    '~~> Your other codeOTHER CODE
SaveItAs:
    
    fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="xls Files (*.xls), *.xls")

    If fileSaveName <> "False" Then
        ActiveWorkbook.SaveAs fileSaveName
    End If


   End Sub

So this one is now resolved! Thank you Koolsid, I think of you as my VBA mentor.

[Siddharth Rout]

Though the problem is solved but I am not too happy with the code...

What exactly are you trying to do? Explain the steps one by one

Sid

[Resource Dragon]

Dear Sid

My code lives in an excel 2003 file called “Report Generator.xls”. The code in this file takes data from various exports from a mainframe system and puts them into a summary format. With the code I wrote for my own use it then saves the summary into a pre-determined folder in my G drive.

Because my colleague won’t have the same G drive structure as I have, I want to amend the code so that she can save the summary using the “save as” that MS Excel provides – as stated earlier, ideally, with the file name already sitting in the dialogue box. (The code I used for moving the summary sheet is code that I “borrowed” from elsewhere .)
Agreed, the code looks a bit “clunky” – but it does do the job.

Cheers

The Dragon

[Siddharth Rout]

Fair Enough