prompt to save as not working in macro

[thelad]

Hi,

I have macro that moves sheets from one workbook to another and deltes certain contents of the moved sheet. I then rename the tabs on the two sheets. It is working fine.

However i would like at the end for a save as prompt to appear, so i can save it to a location. I have code for this and save as does appear but it doesnt seem to be saving the file.

Any ideas? It would appear it opens the save as before the file even opens itself. and when i do save as say workings, it will still be called book1.

Here is my code, any help is appreciated.

Sub CommandButton1_Click()
Dim OutlookApp As Object
Dim MItem As Object
Dim Wb As Workbook
Dim NewWb As Workbook
'Dim I As Long
Dim lastCol As Long
Dim delCol As Long
Dim myCols
Dim sName As String
Dim fil As Variant
 
Application.ScreenUpdating = False
 
 'Create Excel sheet link
Set Wb = ActiveWorkbook
 
Sheets(Array(Sheet2.Name, Sheet3.Name)).Copy
Set NewWb = ActiveWorkbook

myCols = Array("ID Number", "Par", "Identifier", "Date")
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
 
    For I = 0 To UBound(myCols)
        For delCol = lastCol To 1 Step -1
            If Cells(1, delCol) = myCols(I) Then
                Cells(1, delCol).EntireColumn.Delete
            End If
       Next
    Next I
sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2)
ActiveSheet.Name = sName
Worksheets("Sheet3").Name = "Ole Upload"
fil = Application.GetSaveAsFilename(FileFilter:="microsoft excel files (*.xls), *.xls")
If fil <> False Then
MsgBox "File Saved as " & fil
End If
        
Application.ScreenUpdating = True
        
        
End Sub

 

[bjurney]

You will need to add fil.Save to it

If fil <> False Then
 fil.Save
 MsgBox "File Saved as " & fil   'and/or depending on what you tying to do
 End If

Your code, only pulls up the dialouge box, which only sets the name and directory of the file, but doesnt actually save it.

fil would equal C:\excel.xls
fil.save would actually save it

 

[Leith Ross]

Hello thelad,

The Application.GetSaveAsFilename method only returns the selected file name and path that you chose. Despite the name, it does not save the file. Here is the amended the routine...

fil = Application.GetSaveAsFilename(FileFilter:="microsoft excel files (*.xls), *.xls")
If fil <> False Then
   ThisWorkbook.Saves fil
   MsgBox "File Saved as " & fil
End If

Sincerely,
Leith Ross

 

[Zack Barresse]

fil would equal C:\excel.xls
fil.save would actually save it

Not quite. As Leith points out, use it as the string (although I believe he is missing an "a" in "SaveAs"). So using a string as an object would return an error. But passing the string as the file name to save does work.

 

[bjurney]

Not quite. As Leith points out, use it as the string (although I believe he is missing an "a" in "SaveAs"). So using a string as an object would return an error. But passing the string as the file name to save does work.

Ah you're right, I just tested it, should be:

ThisWorkBook.SaveAs Filename:=fil

 

[Leith Ross]

Hello Zack,

Thanks for catching that. Seems I can't type today.

Sincerely,
Leith Ross

 

[Zack Barresse]

No worries, just hope you do the same for me!

 

[thelad]

Hi,

Thanks for your comments. It worked a treat. just had to change the active workbook to the new workbook.

thanks again