|I think this code does what you asked for. Please refer to the comments I have included in the code and see if they match your requirements.|
Since I don't know what the "Before & After" for Column A should look like, I simply let the code place the MID formula in the cell and let Excel process the text in the cell above it. The MID function could be processed within the VBA code itself and then the resulting text string could be placed in Column A, but I have no way of testing that since I don't know what your original data looks like or what it should look like after the MID function. Thus, I let VBA put the formula that you wrote in the new Row of Column A.
Right click the Sheet Tab for the sheet you want this code to run in and choose View Code. Paste the code into the pane that opens and test it by using your Column K Drop Downs.
A few words of caution first:
Since you mentioned that multiple users will be using this worksheet, you should be aware that anyone who wants to use the code will need to have macros enabled within their individual Excel application. This is not something you can "force" them to do and you can't force a macro to run on a system that does not have macros enabled. That would be a security issue and is thus prevented.
In addition, it would behoove you to hide and protect the VBA code so that the users can't see it, or more specifically, can't edit/delete it.
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if only the DropDown in Column K was changed and if "Renewed" was chosen
If Target.Cells.Count = 1 And Target.Column = 11 And Target = "Renewed" Then
'If Yes, turn off Events...
Application.EnableEvents = False
'Copy row, insert below
With Cells(Target.Row, 11)
'Set variable for new row (using Target.Row confuses VBA)
newRow = Target.Row
'Place formula in Column A, refering to original Row
Cells(newRow, 1) = _
"=IF(MID(R[-1]C,11,1)="""",MID(R[-1]C,1,10)&"" " _
& "(Rev 1)"",IF(MID(R[-1]C,18,1)="")""," _
& "MID(R[-1]C,1,16)& MID(R[-1]C,17,1)+1&"")""," _
'Clear Columns E & F
Range(Cells(newRow, 6), Cells(newRow, 7)).ClearContents
'Put new dates in Columns H & I
Cells(newRow, 8) = Cells(newRow - 1, 8) + 1
Cells(newRow, 9) = Cells(newRow, 8) + 28
'Set new Drop Down to "Open"
Cells(newRow, 11) = "Open"
Application.EnableEvents = True
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.