If ("Q") = "Text" then multiple functions help (2007)

Good evening everyone,
I have an unfinished VB code that is troubling to me and trying different things.
I am trying to make the code work as follow:

I have a validation list in column “Q”.

If “amd” is selected then,

Look what is inserted in Column M then copy and paste the information in Column R by adding a /001 at the end.

Then add a new entire row (below) then look for what is inserted in Column M then copy and paste the information in the next row in column M but by following the number from the row above.

Therefore, if the row above is test/001/HS, then add the next number to the next row as follows, Test/002/HS, this must be inserted in the next row that was created.

On this new row it must copy the information and, I presume, an offset from Column A to K. The main row that was selected must then transfer itself to a sheet called “Archives” from column A to X to the next available row in this “Archives” sheet. I have a clear code and sort code together that I want to insert afterward.

I am playing with a pass code that I am using within my sheets that I need it to do what is mentioned above.

If Not Intersect(Target, Range("L5:L10000")) Is Nothing Then
Application.EnableEvents = False
    For I = 1 To Target ' stops the code looping
        tmpArr = tmpArr & "," & I
    Next I
    With Target
        With .Offset(, 1).Resize(.Value)
            .NumberFormat = "000"
            .Value = Application.Transpose(Split(Mid(tmpArr, 2), ","))
            .Formula = "=A$" & .Row & "&""/""&TEXT(ROW()-" & .Row - 1 & ",""000""""/HS"""""")" 'Plus /001
           '.Formula = "=A$" ...is not the right code should be "=M$"
            .Value = .Value
        End With
        .Offset(0, -11).Copy .Offset(0, -11).Resize(.Value)
     'Offset should be from 1 to 11 (Column A to K)  

    End With
    Application.EnableEvents = True
End If

Every time I play around with my code there is always a problem. The code posted is used for something else but I’m posting it to give an idea of kind of what it is.

My code starts with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim TCol As Long
Dim TRow As Long
Dim RptProjRowNum As Long
On Error Resume Next
TRow = Target.Row
TCol = Target.Column
.
.
.
.

If Not Intersect(Target, Range("Q:Q")) Is Nothing Then
If Target.Cells.Count = 1 Then
If LCase(Target.Value) = "amd" Then

Msg = MsgBox("This will create... Continue?", vbYesNo + vbQuestion + vbSystemModal, "Excel Report")
If Msg = vbYes Then


(Code to be inserted here)






Else
'If "No" is selected from the popup menu then it will cancel the popup msg and delete the selection in column M
    Cells(TRow, TCol) = ""
End If
End If
End If
End If