Hi guys,
I have an excel workbook that I use as a quotation template with three sheets, I have a Macro that forces "Save As" and disables "Save" to ensure that the user saves a new version of their "Quotation" for easy retrieval (I will paste the SaveAs macro below).

Now I would like to include a quotation number that will automatically change every time the documet is opened. Eg: if the user opens the quotation template and the last quote number was MTBP 8/17 the new quote number on the opened template will be MTBP 9/17;
MTBP: Company Initials
8:Quote Number
/17: Quote Year

To this point I am successful, however this only work if I continue "Saving" over the same template and not "SaveAs". Basically if I use "Save As" the newly saved quotation will have the updated quote number and the template will still carry the number it originally had;
Quote Template Quote #: MTBP 8/17
New Saved As Quote #: MTBP 9/17

Can any of you guys help me with this, if it is possible?

SAVE AS MACRO:

Dim xFileName As String

SaveAs:
If SaveAsUI <> True Then
xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
Else
ans = MsgBox("Are you sure you want to Cancel?", vbYesNo, "Cancel Alert")
Select Case ans
Case vbYes: Cancel = True: Exit Sub
Case vbNo: GoTo SaveAs
End Select
End If
End If

End Sub

SEQUENTIAL QUOTE # MACRO

Private Sub Workbook_Open()
Range("D2").Value = Range("D2").Value + 1
End Sub

NB I use the following formula to get my desires quote number layout;

="MTBP"&" "&D2&"/"&TEXT(TODAY(),"YY")

I will appreciate your generous assistance.


Kind regards,
OB