Saving a new version of spreadsheet to a folder using VBA

[gt213]

I have a spreadsheet with c20 sheets in it and a lot of VB code.

I want to have a macro that will save a copy of the spreadsheet (with name = value in cell B3 of sheet 1) in the folder:
G:\Indv\EQA\Rec. I'd also like to have the new version saved without the VB code in it, and as an xlsx.

Once the macro is run i want to close the new version of the spreadsheet (saved as above) and continue working in the original version. (e.g. if i were to run the macro then make some changes to the spreadsheet and click save, it would save the changes to the original version).

Hope that makes sense.

 

[Johnny C]

Sounds simple enough...

On Error Resume Next
SuccessSave = ActiveWorkbook.SaveAs Filename:= "G:\Indv\EQA\Rec"&Sheets("Sheet1").range("B3").Value&".xlsx" FileFormat:=xlWorkbookNormal ConflictResolution:=xlUserResolution AddToMru:=True
If Err.Number <> 0 Then
    On Error Goto 0
    Msgbox("Unable to save workbook")
Else
    On Error Goto 0
End If

The Conflict resolution parameter - if the file already exists, it will ask you if you want to overwrite. You can force it to overwrite with =xlLocalSessionChanges
AddtoMRU - will add to recently Used files list.

HTH

 

[gt213]

Sounds simple enough...

On Error Resume Next
SuccessSave = ActiveWorkbook.SaveAs Filename:= "G:\Indv\EQA\Rec"&Sheets("Sheet1").range("B3").Value&".xlsx" FileFormat:=xlWorkbookNormal ConflictResolution:=xlUserResolution AddToMru:=True
If Err.Number <> 0 Then
    On Error Goto 0
    Msgbox("Unable to save workbook")
Else
    On Error Goto 0
End If

The Conflict resolution parameter - if the file already exists, it will ask you if you want to overwrite. You can force it to overwrite with =xlLocalSessionChanges
AddtoMRU - will add to recently Used files list.

HTH

Thanks Johnny, i came across a similar solution elsewhere but without the conflict resolution, which i like and will add in to my code.