Help with Save File Macro

[Aussie5380]

Hi there,

I have a macro written for one of my spreadsheets that saves a copy of the workbook for another staff member to look at, however when the file is saved, it seems to rename the current file in use, and any other data thats entered into the calculator, saves under the information in the calc prior.

Example:

Cal is used to calculate O/payment for John SMITH.

Calc is completed, macro button is hit to save a copy of the file to a staff members save location.

details are then overwritten in the calc (next overpayment is for sally newman)

details are entered for sally, and calc saved again, but it seems to be pulling the info from the last save (with JOHN SMITHS info)

Below is the code i have written. Im just not too sure if i have written something wrong in the macro?

Sub allow_user_to_select_save_location()

    Dim FileSaveName As String
    Dim fname       As String
ActiveWorkbook.Save
    fname = "Name Here"
    FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fname, filefilter:="Excel Files(*.xlsm),*.xlsm", _
                   Title:="Please Select Location To Save File")
    ActiveWorkbook.SaveAs filename:=FileSaveName, FileFormat:=52
End Sub

i have a feeling it may have to do with the last few lines, specifically "ActiveWorkbook.SaveAs filename:=FileSaveName, FileFormat:=52"?

Any assistance would be greatly appreciated!

Thanks heaps in advance

 

[HaHoBe]

Hi Aussie5380,

what about using ActiveWorkbook.SaveCopyAs instead of ActiveWorkbook.SaveAs.

Ciao,
Holger

 

[Aussie5380]

You sir are a gentleman and a scholar! thank you heaps, that's worked perfectly