I have the following code that will autonumber based upon the estimators name, the year and the next sequential number on a sheet named "VariNum". The problem I am having is that this main workbook is a template and is renamed each time an estimate is created. When this happens the sequential number is reset to the original number before the sheet was renamed.
For example...I go into the estimate template and number the estimate 1013 then rename and save.
When I go back into the template to create a new estimate and number the estimate it wants to number as 1013.
How do I fix this?
Thanks
For example...I go into the estimate template and number the estimate 1013 then rename and save.
When I go back into the template to create a new estimate and number the estimate it wants to number as 1013.
How do I fix this?
Code:
Sub CreateEstimate()
Set wsEST = Sheets("Estimate")
ITName = Sheets("Input Form").Range("G34").Value & "-" & Sheets("Input Form").Range("J1").Value & "-" & Sheets("VariNum").Range("A1").Value
Msg = "Do you want to input this Number" & vbCrLf _
& "" & ITName & vbCrLf _
& " into your Estimate Sheet?"
Ans = MsgBox(Msg, vbYesNo + vbQuestion, "New Number?")
If Ans = vbNo Then
Exit Sub
Else
Sheets("VariNum").Range("A1").Value = Sheets("VariNum").Range("A1").Value + 1
wsEST.unprotect
Sheets("Estimate").Range("C2").Value = ITName
Sheets("Estimate").Select
End If
MsgBox "A New Number has been Input." & vbCrLf _
& vbCrLf _
& ITName & vbCrLf _
& vbCrLf _
& "is now on your Estimate page."
End Sub
Thanks