How would I write a scrtip to insert a row under the number in A that holds a 
"NS" or "MT"?  I would also like it to copy the row that has "NS" or "MT" in 
it and paste it in the row that was inserted underneith.  When it copies the 
row it will copy the whole row except what is in A.

Example Before
     A       B
1   MT     12
2   D        14
3   x        15


Example After
       1          2
1    MT       12
2               12 
3    D         14
4    X         15


Thanks

Sub CopyInsert()
'Find the last cell in column A
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = LastRow To 1 Step -1
If Cells(i, 1).Value = "NS" Or Cells(i, 1).Value = "MT" Then
    'Copy row
    Cells(i, 1).EntireRow.Copy
    'Insert new row
    Cells(i + 1, 1).Insert Shift:=xlDown
    'Clear the inserted row's A cell
    Cells(i + 1, 1).ClearContents
End If
Next

End Sub

-- 
Best Regards,

Luke M
"Jeremy" <Jeremy@discussions.microsoft.com> wrote in message 
news:7BC6E044-30E9-413A-86F2-F88BF4A3B8A2@microsoft.com...
> How would I write a scrtip to insert a row under the number in A that 
> holds a
> "NS" or "MT"?  I would also like it to copy the row that has "NS" or "MT" 
> in
> it and paste it in the row that was inserted underneith.  When it copies 
> the
> row it will copy the whole row except what is in A.
>
> Example Before
>     A       B
> 1   MT     12
> 2   D        14
> 3   x        15
>
>
> Example After
>       1          2
> 1    MT       12
> 2               12
> 3    D         14
> 4    X         15
>
>
> Thanks
> 

Jeremy

try this

Sub Insert_Copy()
Set Sht = Sheets("Sheet1") 'Change to suit
Dim x As Long
MyColumn = "A"
For x = Sht.Cells(Rows.Count, MyColumn).End(xlUp).Row To 1 Step -1
    If UCase(Sht.Cells(x, MyColumn)) = "MT" _
    Or UCase(Sht.Cells(x, MyColumn)) = "MS" Then
        Sht.Rows(x + 1).insert
        Sht.Rows(x).Copy Destination:=Cells(x + 1, 1)
        Sht.Cells(x + 1, 1).ClearContents
    End If
Next x
End Sub

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Jeremy" wrote:

> How would I write a scrtip to insert a row under the number in A that holds a 
> "NS" or "MT"?  I would also like it to copy the row that has "NS" or "MT" in 
> it and paste it in the row that was inserted underneith.  When it copies the 
> row it will copy the whole row except what is in A.
> 
> Example Before
>      A       B
> 1   MT     12
> 2   D        14
> 3   x        15
> 
> 
> Example After
>        1          2
> 1    MT       12
> 2               12 
> 3    D         14
> 4    X         15
> 
> 
> Thanks
>